MySQL删除表中多余的重复记录，只保留一条记录

来源：互联网发布：金庸群侠传x数据上限编辑：程序博客网时间：2024/05/17 23:54

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_PersonWHERE pName IN (SELECT pName         FROM (SELECT pName               FROM T_Person               GROUP BY pName HAVING COUNT(pName)>1        ) AS temp);

这里写图片描述

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

DELETE FROM T_PersonWHERE 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);

这里写图片描述

转载：http://lsieun.blog.51cto.com/9210464/1813903

0 0