1、查询重复记录

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

2、删除重复记录，但保留一条

DELETE FROM 表名
WHERE 重复字段 IN (SELECT 重复字段 
           FROM 表名
           GROUP BY 重复字段 HAVING COUNT(重复字段)>1)
      AND Id NOT IN (SELECT MIN(id) 
                     FROM 表名
                     GROUP BY 重复字段 HAVING COUNT(重复字段)>1);

3、案例

数据准备

CREATE TABLE T_Person(
pId INT PRIMARY KEY AUTO_INCREMENT,
pName VARCHAR(20)
);

INSERT INTO T_Person(pName) VALUES('陈一');
INSERT INTO T_Person(pName) VALUES('黄二');
INSERT INTO T_Person(pName) VALUES('张三');
INSERT INTO T_Person(pName) VALUES('张三');
INSERT INTO T_Person(pName) VALUES('李四');
INSERT INTO T_Person(pName) VALUES('李四');
INSERT INTO T_Person(pName) VALUES('王五');
INSERT INTO T_Person(pName) VALUES('赵六');
INSERT INTO T_Person(pName) VALUES('赵六');
INSERT INTO T_Person(pName) VALUES('钱七');
INSERT INTO T_Person(pName) VALUES('王五');
INSERT INTO T_Person(pName) VALUES('李四');
INSERT INTO T_Person(pName) VALUES('孙八');
INSERT INTO T_Person(pName) VALUES('杨九');
INSERT INTO T_Person(pName) VALUES('张三');
INSERT INTO T_Person(pName) VALUES('吴十');

SELECT * FROM T_Person;

上面共16条数据，查看包含重复姓名的记录都有哪些？

SELECT pName FROM T_Person GROUP BY pName HAVING COUNT(pName)>1;

情况1和情况2都是原来的16条数据的基础上做的。

情况1：删除所有重复数据，SQL语句如下：

DELETE FROM T_Person
WHERE pName IN (SELECT pName 
        FROM (SELECT pName 
              FROM T_Person 
              GROUP BY pName HAVING COUNT(pName)>1
        ) AS temp);

情况2：重复的数据只留一条，其它重复数据删除，SQL语句如下：

DELETE FROM T_Person
WHERE pName IN (SELECT pName 
        FROM (SELECT pName 
              FROM T_Person 
              GROUP BY pName HAVING COUNT(pName)>1
        ) AS temp)
      AND 
      pId NOT IN(
        SELECT pId 
        FROM (SELECT MIN(pId) AS pId,pName
              FROM T_Person
              GROUP BY pName HAVING COUNT(pName)>1
              ) AS temp2);

4.我比较喜欢使用的

DELETE c1 FROM 数据库表名 c1 INNER JOIN 数据库表名 c2 WHERE c1.id > c2.id AND c1.重复字段 = c2.重复字段;