MySQL删除重复数据并保留最新的一条,网上有些博客写的有问题,因为他们只考虑到或者说他们的重复数据最多只有两条,当重复数据大于2时,就会出现问题。
案例描述:
按天删除表中重复数据只保留最新的一条(这里按照id越大,数据越新),注意,一天中可能出现多条数据。重复数据以name为准,即name相同视为重复
案例表结构和数据
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`create_date` datetime NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (1, '2019-04-17 00:00:01', '1');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (2, '2019-04-17 00:00:03', '1');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (3, '2019-04-17 00:00:06', '3');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (4, '2019-04-18 00:00:04', '2');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (5, '2019-04-17 00:00:01', '1');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (6, '2019-04-18 00:00:00', '2');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (7, '2019-04-18 00:00:03', '1');
INSERT INTO `test`.`test`(`id`, `create_date`, `name`) VALUES (8, '2019-04-18 00:00:04', '1');
SQL
DELETE FROM test WHERE
id IN
(
SELECT a.id FROM test a WHERE a.NAME IN
(
SELECT b.NAME FROM test b GROUP BY b.NAME, DATE_FORMAT( b.create_date, '%Y-%m-%d' ) HAVING count( DATE_FORMAT( b.create_date, '%Y-%m-%d' ) ) > 1
)
)
AND id NOT IN
(
SELECT max( d.id ) id FROM test d GROUP BY d.NAME, DATE_FORMAT( d.create_date, '%Y-%m-%d' ) HAVING count( DATE_FORMAT( d.create_date, '%Y-%m-%d' ) ) > 1
);
分析
根据案例描述,按天删除重复数据,以name相同视为重复,并且只保留最新的一条
以时间和name分组,并通过having过滤每种重复的name数量大于1的name,注意:group by的第一个参数一定要是时间
以步骤1的结果集作为条件,查询表所有重复数据的id
同步骤1,但是查询的是重复数据的最大id
删除数据