mysql(16) : 根据多个字段删除重复数据

最新推荐文章于 2024-04-17 15:25:46 发布

楽焫旒璡

最新推荐文章于 2024-04-17 15:25:46 发布

阅读量4k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： # mysql 文章标签：根据多个字段删除重复数据 mysql删除重复数据删除重复数据

原文链接：https://www.cnblogs.com/liyue-sqsf/p/9076902.html

mysql 专栏收录该内容

60 篇文章

订阅专栏

本文提供了一种使用SQL语句高效删除数据库中重复记录的方法，包括针对单个字段和多个字段的重复情况，避免了低效的逐条删除方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考 :

https://blog.youkuaiyun.com/n950814abc/article/details/82284838

https://www.cnblogs.com/liyue-sqsf/p/9076902.html

单个字段(CODE)

DELETE FROM `TEST` WHERE `ID` IN(
    SELECT * FROM(
        SELECT `ID` FROM `TEST` WHERE `CODE` IN (
            SELECT `CODE` FROM TEST GROUP BY `CODE` HAVING COUNT(*)> 1
        )
        AND `ID` NOT IN(
            SELECT MIN(`ID`)FROM TES GROUP BY `CODE` HAVING COUNT(*)> 1
        )
    ) T
);

多个字段(CODE,NAME)

DELETE FROM `TEST` WHERE `ID` IN(
    SELECT * FROM(
        SELECT `ID` FROM `TEST` WHERE (`CODE`,`NAME`) IN (
            SELECT `CODE`,`NAME` FROM `TEST` GROUP BY `CODE`,`NAME` HAVING COUNT(*)> 1
        )
        AND `ID` NOT IN(
            SELECT MIN(`ID`)FROM `TEST` GROUP BY `CODE`,`NAME` HAVING COUNT(*)> 1
        )
    ) T
);

低效率(该SQL一次只会删除重复的一条数据,需要执行n次,不推荐)

DELETE FROM `TEST` WHERE `ID` IN (
    SELECT * FROM (
        SELECT `ID`  FROM `TEST` GROUP BY `CODE` ,`NAME` HAVING COUNT(*) > 1
    )L
);

测试数据

1.表结构

CREATE TABLE `TEST` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `CODE` VARCHAR(32) DEFAULT NULL,
  `NAME` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=20 DEFAULT CHARSET=UTF8;

2.测试数据

TRUNCATE TABLE `TEST`;
INSERT INTO `TEST`(`ID`,`CODE`,`NAME`) VALUES
(1,'C1','N1'),
(2,'C2','N2'),
(3,'C2','N2'),
(4,'C2','N21'),
(5,'C2','N21'),
(6,'C3','N3'),
(7,'C3','N3'),
(8,'C3','N3'),
(9,'C3','N31'),
(10,'C3','N31'),
(11,'C3','N31'),
(12,'C4','N4'),
(13,'C4','N4'),
(14,'C4','N4'),
(15,'C4','N4'),
(16,'C4','N41'),
(17,'C4','N41'),
(18,'C4','N41'),
(19,'C4','N41');

END。