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