MySQL查询重复记录,删除重复记录方法

善良没用，因为只有你先漂亮，别人才能看到你的善良。

命令

查找全部重复记录

1	SELECT * FROM 表 WHERE 重复字段 IN (SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*)>1);

过滤重复记录(只显示一条)

1
2

SELECT * FROM 表 WHERE ID IN (SELECT MAX(ID) FROM 表 GROUP BY Title);     #显示ID最大一条记录
SELECT * FROM 表 WHERE ID IN (SELECT MIN(ID) FROM 表 GROUP BY Title);     #显示ID最小一条记录

删除全部重复记录

1	DELETE 表 WHERE 重复字段 IN (SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*)>1);

删除全部重复记录保留最大ID最大一条记录

1
2

DELETE FROM 表 WHERE ID NOT IN (SELECT MAX(ID) FROM 表 GROUP BY Title)    #保留ID最大一条记录
DELETE FROM 表 WHERE ID NOT IN (SELECT MIN(ID) FROM 表 GROUP BY Title)    #保留ID最小一条记录

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

DELETE FROM vitae a WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*)>1)

查找表中多余的重复记录（多个字段），不包含rowid最小的记录

SELECT * FROM vitae a WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*)>1)

查找表中多余的重复记录（多个字段）

1	SELECT * FROM vitae a WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1)

总结

说明：

单表的唯一查询用：distinct
多表的唯一查询用：group by
distinct 查询多表时，left join 还有效，全连接无效