MySQL表数据去重几个业务逻辑实现

1、创建测试表`group`

CREATE TABLE `group` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名字',
  `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk COMMENT='group';

2、导入测试数据

insert into `group`(name, addr) values('a', 'a1'),('b', 'b1'),('a', 'a2'),('b', 'b2');

3、求name重复id最大的数据结果

MySQL> select name, addr,max(id) from `group` group by name;  错误

mysql> select id, name, addr from `group` where id in (select max(id) from `group` group by name);  正确

mysql> select t2.maxid as id, t1.name, t1.addr from `group` t1 inner join (select max(id) as maxid,name from `group` group by name) t2 on t1.id = t2.maxid; 正确

4、删除name重复数据，保留id最大的数据。(先备份再操作)

mysql> delete from `group` where id not in (select max(id) as id from `group` group by name);  错误

mysql> delete from `group` where id not in (select t1.id from (select max(id) as id from `group` group by name) t1); 正确

5、查询name重复数据结果

mysql> select name,count(1) as total from `group` group by name having total > 1;