【mysql】mysql删除重复记录并且只保留一条

准备的测试表结构及数据

插入的数据中A,B,E存在重复数据,C没有重复记录

CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tab
-- ----------------------------
INSERT INTO `tab` VALUES ('1', 'A');
INSERT INTO `tab` VALUES ('2', 'A');
INSERT INTO `tab` VALUES ('3', 'A');
INSERT INTO `tab` VALUES ('4', 'B');
INSERT INTO `tab` VALUES ('5', 'B');
INSERT INTO `tab` VALUES ('6', 'C');
INSERT INTO `tab` VALUES ('7', 'B');
INSERT INTO `tab` VALUES ('8', 'B');
INSERT INTO `tab` VALUES ('9', 'B');
INSERT INTO `tab` VALUES ('10', 'E');
INSERT INTO `tab` VALUES ('11', 'E');
INSERT INTO `tab` VALUES ('12', 'E');

使用HAVING关键字筛选出表中重复数据

SELECT `name`,COUNT(1) FROM TAB GROUP BY `name` HAVING COUNT(1) >1

 

可以通过分组语句从每种重复数据中都拿出一条标识

SELECT `name`,id  FROM TAB GROUP BY `name`  HAVING COUNT(1) >1

删除重复记录并且只保留一条 [留意SQL注释] 

DELETE from tab where 
-- 删除所有的重复时间 Begin --
`name` in (
	SELECT * from (SELECT `name`FROM TAB GROUP BY `name`  HAVING COUNT(1) >1) tmp2
)
-- 删除所有的重复时间 END --
-- 但一些特定ID的记录不进行删除  Begin --
AND
id NOT in(
	select id from (
		SELECT `name`,id  FROM TAB GROUP BY `name` HAVING COUNT(1) >1
	) tmp1
) 
-- 但一些特定ID的记录不进行删除  END --

 

执行后最终结果