日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

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;