MySQL删除重复数据只保留最新的一条

素颜马尾好姑娘i 2021-06-12 20:36 538阅读 0赞

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  
删除数据