SQL语句删除重复记录且只保留一条有效记录

最新推荐文章于 2024-05-22 10:48:33 发布

陈皮dd

最新推荐文章于 2024-05-22 10:48:33 发布

阅读量8.2k

点赞数 6

文章标签：数据库 mysql Powered by 金山文档

本文链接：https://blog.csdn.net/qq_45058475/article/details/128597176

版权

1.查找表中多余的重复记录，重复记录是根据单个字段（name）来判断

select * from lib
where name in (select name from lib group by name having count(name) > 1)

2.删除表中多余的重复记录，重复记录是根据单个字段（id）来判断，只留有id最小的记录

delete from lib
where name in (select name from (select name from lib group by name having count(name) > 1) as a)
and id not in (select min_id from (select min(id) as min_id from lib group by name having count(name)>1) as b)

3.删除表中多余的重复记录（多个字段），只留有id最小的记录

DELETE FROM lib WHERE (`name`, version) IN 
(SELECT t.`name`, t.version FROM
   (SELECT `name`, version FROM lib GROUP BY `name`, version HAVING count(1) > 1 ) t)
AND id NOT IN ( SELECT dt.minid FROM
   (SELECT min(id) AS minid FROM lib GROUP BY `name`,  version HAVING count(1) > 1 ) dt);

参考：https://blog.csdn.net/JineD/article/details/126875291