mysql保留重复数据的最大id

一、需求

一张user表，主键自增，姓名可重复，现在将姓名重复的数据保留最大的id，不重复的不要动。

CREATE TABLE `test_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(4) DEFAULT NULL,
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '1' COMMENT '1-可用、0-不可',
  PRIMARY KEY (`id`)
);

INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (1, '张三', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (2, '张三', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (3, '张三', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (4, '张三', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (5, '张三', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (6, '李四', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (7, '李四', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (8, '李四', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (9, '李四', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (10, '李四', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (11, '王一', '1');
INSERT INTO `park_platform`.`test_user`(`id`, `name`, `status`) VALUES (12, '赵二', '1');

二、实现

1.首先查出重复数据的最大id

SELECT name,MAX(id) maxId
FROM test_user GROUP BY name HAVING COUNT(id) >= 2;

 

2.关联查询查出重复数据中小于最大id的数据

SELECT test_user.id AS id FROM test_user,
(
SELECT name,MAX(id) maxId
FROM test_user GROUP BY name HAVING COUNT(id) >= 2 )tempt
WHERE test_user.name = tempt.name
AND test_user.id < tempt.maxId;

 3.删除。注意：这里需要新建临时表tt，不能在上一步的上直接操作，否则报错1093 - You can't specify target table 'test_user' for update in FROM clause

UPDATE test_user SET `STATUS`='0' WHERE id in(
select * FROM
(SELECT test_user.id FROM test_user,
(
SELECT name,MAX(id) maxId
FROM test_user GROUP BY name HAVING COUNT(id) >= 2 )tempt
WHERE test_user.name = tempt.name
AND test_user.id < tempt.maxId) tt
);