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

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

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

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

delete from libwhere 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);

参考：/JineD/article/details/126875291