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;