语句删除重复记录且只保留一条有效记录
-
首先要查看是否有重复的数据：
- 根据 F_MemberIdCard 判断：
SELECT COUNT(F_MemberIdCard), F_MemberIdCard FROM 表名 GROUP BY F_MemberIdCard HAVING COUNT(F_MemberIdCard) > 1;- 根据最早插入时间判断：
SELECT MIN(update_time) FROM 表名 GROUP BY F_MemberIdCard HAVING COUNT(*)>1; -
查询表中全部多余的重复的数据，
- 重复记录是根据单个字段（F_MemberIdCard ）来判断
SELECT * FROM 表名 WHERE F_MemberIdCard IN (SELECT F_MemberIdCard FROM 表名 GROUP BY F18 HAVING COUNT(*)>1);2.查询表中多余的重复记录，重复记录是根据单个字段（update_time ）来判断，只留有update_time 最早的记录
SELECT * FROM 表名 WHERE F_MemberIdCard IN (SELECT * FROM (SELECT F_MemberIdCard FROM 表名 GROUP BY F_MemberIdCard HAVING COUNT(*)>1) a) AND update_time NOT IN (SELECT * FROM (SELECT min(update_time) AS update_time FROM 表名 GROUP BY F_MemberIdCard 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);
参考文档
