SQL---查找+删除重复记录

查找重复记录：

 

1，重复记录根据单个字段来判断

在People表中，a列中的值重复即算重复记录：

SELECT a, COUNT(*)
FROM People
GROUP BY a
HAVING COUNT(*) > 1

 

2，重复记录根据多个字段来判断

在People表中，a列和b列中的值都重复即算重复记录：

SELECT a, b, COUNT(*)
FROM People
GROUP BY a, b
HAVING COUNT(*) > 1

 

在People表中，a列或b列中的值有一个重复即算重复记录：

SELECT a AS col, COUNT(*)
FROM People
GROUP BY a
HAVING COUNT(*) > 1
UNION ALL
SELECT b AS col, COUNT(*)
FROM People
GROUP BY b
HAVING COUNT(*) > 1

 

删除重复记录：

 

删除Peope表中重复的记录，只保留最小的id对应的记录：

1，把重复记录中不需要删除的记录创建临时表；2，删除原表中的记录

CREATE TEMPORARY TABLE to_delete (id INT NOT NULL, a VARCHAR NOT NULL);

INSERT INTO to_delete(id, a)
    SELECT MIN(id), a FROM People GROUP BY a HAVING COUNT(*) > 1

DELETE FROM People
WHERE EXISITS (SELECT * FROM to_delete
         WHERE to_delete.a = People.a AND to_delete.id != People.id)