sql中group by 应用在子查询的情况

 今天一个同事不小心把我们的权限表都更新成了一样的角色 ，也就是user_role_mapping插入了相同的user_id 和role_id 是一样的遇到了一个奇怪的问题，下面就是问题和分析解决的过程。

下面是表结构

CREATE TABLE `user_role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `role_id` int(11) NOT NULL DEFAULT '0' COMMENT '角色id',
  `app_source` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_role_id` (`role_id`),
) ENGINE=InnoDB AUTO_INCREMENT=150846 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户角色映射表'

也就是这个表里面存在user_id 和role_id 都相同的多个数据。需要删除重复的数据，只保留一个user_id 和role_id的映射,并且app_source='XXXX'。

问题

1.首先我们用group by user_id 找到一个有多少个用户和用户的id

select id from user_role where app_source='xxx' group by user_id; 

2.把删除除了这些id 的并且app_source='XXXX'的数据

在删除之前我们先查询一遍要删除的数据有多少,总数是526，要删除51条数据

需要删除的数据,怎么还是526，看花眼了？

select  count(*) from user_role  where id in 
(select  id from user_role  where app_source='xxx' group by user_id)
 and app_source='xxx';

分析解决

根据select id from user_role where app_source='xxx' group by user_id; 查询出来的id 是51条数据，但是在子查询里面却是全量526

select  count(*) from user_role  where id in 
(select  id from user_role  where app_source='xxx' group by user_id)
 and app_source='xxx';

纳尼，见鬼了！！！

从网上查询:group by user_id 之后，查询其他字段如id，只会随机分组后，如果select子句想取出某个字段，那么它不可能将都属于user_id1组的记录都取出来。只是从user_id1组中取出一个代表性的值出来。不知道原因，大概是：已经按照group by的规则进行分组后，如果想取出所有数据，mysql变得无所适从。group by只有在需要进行统计的的时候非常有效。 这个值得探究的问题

 

后来看到可以根据查询每个类别下id最大值来解决问题上面的sql 改下就可以了。

select  count(*) from user_role  where id in 
(select MAX (id) from user_role  where app_source='xxx' group by user_id)
 and app_source='xxx';

或者

select  count(*) from user_role  where id in 
(select MIN (id) from user_role  where app_source='xxx' group by user_id)
 and app_source='xxx';

用了MAX(id) 说明分组找到每组里面最大的id ， MIN (id)说明分组找到每组里面最小的id，这样我们查询的数据就和要处理的数据一致的了，没有多，也没有少。

delete from user_role  where id in 
(select  MAX(id) from user_role  where app_source='xxx' group by user_id)
 and app_source='xxx';

 

 

 

 

 

参考文档：https://www.cnblogs.com/wangtao_20/archive/2011/02/23/1959792.html

https://www.cnblogs.com/xiaoxi/p/6734025.html