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

首先要查看是否有重复的数据：

1.0  根据F18判断：

SELECT F18 FROM [dbo].[ZhiLianZhaoPin] GROUP BY F18 HAVING COUNT(*)>1;

2.0 根据最早插入时间判断：

SELECT min(update_time) FROM [dbo].[ZhiLianZhaoPin] GROUP BY F18 HAVING COUNT(*)>1;

1. 查询表中全部多余的重复的数据，重复记录是根据单个字段（F18 ）来判断

SELECT  *  FROM [dbo].[ZhiLianZhaoPin] WHERE F18 IN (SELECT F18 FROM [dbo].[ZhiLianZhaoPin] 

GROUP BY F18 HAVING COUNT(*)>1);

2.查询表中多余的重复记录，重复记录是根据单个字段（update_time ）来判断，只留有update_time 最早的记录

SELECT * FROM [dbo].[ZhiLianZhaoPin] WHERE F18 IN (SELECT * FROM (SELECT F18 FROM [dbo].[ZhiLianZhaoPin] GROUP BY F18 HAVING COUNT(*)>1) a) AND update_time NOT IN (SELECT * FROM (SELECT min(update_time) AS update_time FROM [dbo].[ZhiLianZhaoPin] GROUP BY F18 HAVING count(*)>1) b);

3.删除表中多余的重复记录，重复记录是根据单个字段（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);

4.删除表中多余的重复记录，重复记录是根据多个字段来判断，只留有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);