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

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

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

  1. 根据 F_MemberIdCard 判断：

    SELECT COUNT(F_MemberIdCard), F_MemberIdCard  FROM  表名
    GROUP BY F_MemberIdCard  HAVING COUNT(F_MemberIdCard)  > 1;
  

  2. 根据最早插入时间判断：

  SELECT MIN(update_time) FROM   表名  GROUP BY F_MemberIdCard 
  HAVING     COUNT(*)>1;
  

• 查询表中全部多余的重复的数据，

  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);
  

参考文档

• 参考文档