参考 :
https://blog.csdn.net/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。