mysql对重复记录的查询和删除

删除mysql数据库中重复的数据

 

1.建立一个 t_use 表

CREATE TABLE `t_use` (
  `id` varchar(36) NOT NULL,
  `code` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `project` varchar(255) DEFAULT NULL,
  `score` decimal(16,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据

INSERT INTO `t_use` VALUES ('111111', '1111111', '张三', '语文', '15.00');
INSERT INTO `t_use` VALUES ('222222', '1111111', '张三', '语文', '15.00');
INSERT INTO `t_use` VALUES ('333333', '2222222', '李四', '语文', '86.00');
INSERT INTO `t_use` VALUES ('444444', '2222222', '李四', '语文', '86.00');
INSERT INTO `t_use` VALUES ('555555', '1111111', '张三', '英语', '65.00');
INSERT INTO `t_use` VALUES ('666666', '2222222', '李四', '英语', '65.00');
INSERT INTO `t_use` VALUES ('777777', '3333333', '王五', '语文', '65.00');

3.需求：对于code相同的数据，只保留id最大的一条，其余的删除。

 

4.查询 code 重复的数据

select t1.code from t_use t1 group by t1.code having count(t1.code) > 1

5.查询code重复的数据中id最大的

select max(t2.id) from t_use t2 
where t2.code in (
	select t1.code from t_use t1 
	where t1.code = t2.code having count(t1.code) > 1) 
	group by t2.code

 

6.正确的删除方法(注意：最外层的表不能用别名，否则报错。原因目前还不清楚！望大神解答)

DELETE
FROM t_use  
WHERE id not in (
	SELECT max(e.id)
	FROM (SELECT t1.id FROM t_use t1 GROUP BY t1. CODE HAVING count(t1. CODE) > 1) e
)
and code in (
	SELECT r.code
	FROM ( SELECT t2.code FROM t_use t2 GROUP BY t2. CODE HAVING count(t2. CODE) > 1) r
)

7.方法简化

DELETE
FROM t_use  
WHERE id not in (
	SELECT e.id
	FROM (SELECT max(t1.id) as id FROM t_use t1 GROUP BY t1.CODE ) e
)