MySQL去重，一条SQL语句完美解决【去重留一】

左手的ㄟ右手 2024-03-30 13:36 81阅读 0赞

此处以某消费记录表(consume\_record)为例，SQL语句如下：

DELETE consume\_record  
FROM  
consume\_record,  
(  
SELECT  
min(id) id,  
user\_id,  
monetary,  
consume\_time  
FROM  
consume\_record  
GROUP BY  
user\_id,  
monetary,  
consume\_time  
HAVING  
count(\*) > 1  
) t2  
WHERE  
consume\_record.user\_id = t2.user\_id  
and consume\_record.monetary = t2.monetary  
and consume\_record.consume\_time = t2.consume\_time  
AND consume\_record.id > t2.id;

SQL语句分析：

1、查询出重复记录形成一个集合（临时表t2），集合里是每种重复记录的最小ID

(SELECT min(id) id, user\_id, monetary, consume\_time FROM consume\_record GROUP BY user\_id, monetary, consume\_time HAVING count(\*) > 1 ) t2

2、关联<判断重复基准的字段

consume\_record.user\_id = t2.user\_id and consume\_record.monetary = t2.monetary and consume\_record.consume\_time = t2.consume\_time

3、根据条件，删除原表中id大于t2中id的记录

DELETE consume\_record FROM ... WHERE ... AND consume\_record.id > t2.id;

测试效果：

![a9601b5513a3e70b1e686b750126db99.png][]

![486fff7f0c5c4f20815c618fa22b5b56.png][]

![654fa009d797aa66086a2a99b004e718.png][]

图一为删除前总记录数45541，图二为删除操作、从45541条记录中删除2800条重复记录用时0.09秒，图三为删除后总记录数。贴上测试表，如有需要的小伙伴，下载导入即可进行测试。[consume\_record.sql][consume_record.sql]

[a9601b5513a3e70b1e686b750126db99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/601d48bf2a4f403795efd26a7af5e38f.png
[486fff7f0c5c4f20815c618fa22b5b56.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/35d3eb8e6eff480db4fce0acfeb6be21.png
[654fa009d797aa66086a2a99b004e718.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b6909e9b713f46b0b9103d13875de970.png
[consume_record.sql]: http://blog.diginfos.com/storage/upload/consume_record.sql