mysql如何删除相同记录中的一条记录 | 您所在的位置:网站首页 › mysql中删除重复数据只保留一条记录怎么办 › mysql如何删除相同记录中的一条记录 |
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); 查看结果SELECT * FROM T_Person; |
CopyRight 2018-2019 实验室设备网 版权所有 |