MySQL 删除数据库中重复数据方法小结
作者:hebedich 时间:2024-01-25 16:39:03
刚开始,根据我的想法,这个很简单嘛,上sql语句
delete from zqzrdp where tel in (select min(dpxx_id) from zqzrdp group by tel having count(tel)>1);
执行,报错!!~!~
异常意为:你不能指定目标表的更新在FROM子句。傻了,MySQL 这样写,不行,让人郁闷。
难倒只能分步操作,蛋疼
以下是网友写的,同样是坑爹的代码,我机器上运行不了。
1. 查询需要删除的记录,会保留一条记录。
select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid
2. 删除重复记录,只保留一条记录。注意,subject,RECEIVER 要索引,否则会很慢的。
delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;
3. 查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
4. 删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
5.删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
看来想偷懒使用一句命令完成这个事好像不太显示,还是老老实实的分步处理吧,思路先建立复制一个临时表,然后对比临时表内的数据,删除主表里的数据
alter table tableName add autoID int auto_increment not null;
create table tmp select min(autoID) as autoID from tableName group by Name,Address;
create table tmp2 select tableName.* from tableName,tmp where tableName.autoID = tmp.autoID;
drop table tableName;
rename table tmp2 to tableName;
标签:mysql,删除重复数据
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python实现批量文件重命名
2021-03-25 22:04:38
![](https://img.aspxhome.com/file/2023/5/79585_0s.jpg)
mysql性能优化之索引优化
2024-01-15 13:51:44
![](https://img.aspxhome.com/file/2023/8/110298_0s.png)
python 点云地面点滤波-progressive TIN densification(PTD)算法介绍
2023-05-26 17:50:55
![](https://img.aspxhome.com/file/2023/8/96918_0s.png)
详解如何用OpenCV + Python 实现人脸识别
2021-07-07 19:22:35
![](https://img.aspxhome.com/file/2023/4/76864_0s.jpg)
python 控制语句
2022-09-19 08:12:02
Django中的文件的上传的几种方式
2023-08-26 22:48:51
Pyhton中单行和多行注释的使用方法及规范
2021-11-21 12:13:00
![](https://img.aspxhome.com/file/2023/0/135310_0s.jpg)
如何用MySQL-Front远程连接MySql?
2010-12-03 16:02:00
mysql5.7.17安装使用图文教程
2024-01-19 16:08:43
![](https://img.aspxhome.com/file/2023/5/119765_0s.png)
有关Server.Mappath详细接触
2010-07-07 11:35:00
![](https://img.aspxhome.com/file/UploadPic/20107/7/2004351085047387-42s.gif)
Python实现Matplotlib,Seaborn动态数据图的示例代码
2023-02-03 23:47:22
![](https://img.aspxhome.com/file/2023/5/77745_0s.gif)
Python入门之字典的使用教程
2021-09-15 00:35:12
![](https://img.aspxhome.com/file/2023/8/121758_0s.png)
mysql中复制表结构的方法小结
2024-01-19 22:54:26
浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献
2024-01-14 01:04:27
![](https://img.aspxhome.com/file/2023/5/64845_0s.png)
Python minidom模块用法示例【DOM写入和解析XML】
2021-04-22 23:28:19
python中安装django模块的方法
2023-08-27 13:30:21
![](https://img.aspxhome.com/file/2023/1/65051_0s.png)
javascript将扁平的数据转为树形结构的高效率算法
2024-02-24 05:26:01
![](https://img.aspxhome.com/file/2023/2/56422_0s.png)
WEB开发之注册页面验证码倒计时代码的实现
2024-04-22 22:32:31
![](https://img.aspxhome.com/file/2023/1/135651_0s.png)
介绍Python的Django框架中的QuerySets
2021-04-19 18:58:32
MySQL慢查询之开启慢查询
2024-01-23 16:16:03