MySQL 查询重复数据只保留最新一条

准备工作创建测试表CREATE TABLE `user` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`code` varchar(16) NOT NULL,`name` varchar(32) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;插入数据INSERT IN

dadeity

6049人浏览 · 2021-10-28 16:30:13

dadeity · 2021-10-28 16:30:13 发布

应用场景

程序实际开发中，总会产生重复的数据，后期优化需要删除重复数据

准备工作

创建测试表

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(16) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO user (code,name) VALUES('A','张三'),('A','张三'),('A','张三'),('B','李四'),('B','李四'),('B','李四'),('B','李四');

在这里插入图片描述

DDL操作

查询重复数据及重复条数

SELECT id, code, name, count(code) as count
FROM user
GROUP BY code
HAVING count(code) > 1
ORDER BY count DESC;

在这里插入图片描述

查询重复数据，保留最新一条

SELECT *
FROM user
WHERE id NOT IN (
    # 查询重复数据code对应的最大id
    SELECT id
    FROM (
             # 按id倒序排列
             SELECT *
             FROM user
             ORDER BY id DESC
             LIMIT 100000000) r1
    GROUP BY r1.code
    HAVING COUNT(r1.code) > 1)
  and code IN (
    # 查询重复的code
    SELECT code
    FROM user r2
    GROUP BY r2.code
    HAVING COUNT(r2.code) > 1)
ORDER BY id, code;