mysql返回结果集函数（mysql 判断是否为子集的方法步骤）
mysql返回结果集函数
mysql 判断是否为子集的方法步骤目录
- 一、问题
-
二、解决方案
- 方案一：
- 方案二：
一、问题
故事起源于一个查询错漏率的报表：有两个查询结果，分别是报告已经添加的项目和报告应该添加的项目，求报告无遗漏率
何为无遗漏？即，应该添加的项目已经被全部添加
报告无遗漏率也就是无遗漏报告数占报告总数的比率
这里以两个报告示例（分别是已全部添加和有遗漏的报告）
首先，查出第一个结果——报告应该添加的项目
|
|
select r.id as 报告id,m.project_id 应添加项目from report r inner join application a on r.app_id=a.id inner join application_sample s on a.id=s.app_id right join application_sample_item si on s.id=si.sample_id right join set_project_mapping m on si.set_id=m.set_idwhere r.id in ('44930','44927')order by r.id,m.project_id; |
然后，再查出第二个结果——报告已经添加的项目
|
|
select r.id as 报告id,i.project_id as 已添加项目 from report r right join report_item i on r.id=i.report_idwhere r.id in ('44930','44927'); |
以上就是我们要比较的结果集，不难看出报告44927是无遗漏的，而44930虽然项目数量一致，但实际是多添加了项目758，缺少了项目112，是有遗漏的报告
二、解决方案
从问题看，显然是一个判断是否为子集的问题。可以分别遍历已添加的项目和应该添加的项目，如果应该添加的项目在已添加的项目中都能匹配上，即代表应该添加的项目是已添加的项目子集，也就是无遗漏。
通过循环遍历比较确实可以解决这个问题，但是sql中出现笛卡儿积的交叉连接往往意味着开销巨大，查询速度慢，那么有没有办法避免这一问题呢？
方案一：
借助于函数 find_in_set和group_concat, 首先认识下两个函数
find_in_set(str,strlist)
- str: 需要查询的字符串
- strlist: 参数以英文”,”分隔，如 (1,2,6,8,10,22)
find_in_set 函数返回了需要查询的字符串在目标字符串的位置
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
group_concat()函数可以将多条记录的同一字段的值，拼接成一条记录返回。默认以英文‘,'分割。
但是，group_concat()默认长度为1024
所以，如果需要拼接的长度超过1024将会导致截取不全，需要修改长度
|
|
set global group_concat_max_len=102400;set session group_concat_max_len=102400; |
从上述两个函数介绍中，我们发现find_in_set和group_concat都以英文‘,'分割（加粗标识）
所以，我们可以用group_concat将已添加项目的项目连接为一个字符串，然后再用find_in_set逐一查询应添加项目是否都存在于字符串
1、修改问题中描述中的sql，用group_concat将已添加项目的项目连接为一个字符串
|
|
select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 from report r left join report_item i on r.id=i.report_idwhere r.id in ('44930','44927')group by r.id; |
2、用find_in_set逐一查询应添加项目是否都存在于字符串
|
|
select q.id,find_in_set(w.应添加项目列表,q.已添加项目列表) as 是否遗漏 from ( -- 报告已经添加的项目 select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 from report r left join report_item i on r.id=i.report_id where r.id in ('44930','44927') group by r.id )q, ( -- 报告应该添加的项目 select r.id,s.app_id,m.project_id 应添加项目列表 from report r inner join application a on r.app_id=a.id inner join application_sample s on a.id=s.app_id inner join application_sample_item si on s.id=si.sample_id inner join set_project_mapping m on si.set_id=m.set_id where r.id in ('44930','44927') order by r.id,m.project_id )w where q.id=w.id; |
3、过滤掉有遗漏的报告
|
|
select q.id,case when find_in_set(w.应添加项目列表,q.已添加项目列表)>0 then 1 else 0 end as 是否遗漏 from ( -- 报告已经添加的项目 select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 from report r left join report_item i on r.id=i.report_id where r.id in ('44930','44927') group by r.id )q, ( -- 报告应该添加的项目 select r.id,s.app_id,m.project_id 应添加项目列表 from report r inner join application a on r.app_id=a.id inner join application_sample s on a.id=s.app_id inner join application_sample_item si on s.id=si.sample_id inner join set_project_mapping m on si.set_id=m.set_id where r.id in ('44930','44927') order by r.id,m.project_id )w where q.id=w.id group by q.id having count(`是否遗漏`)=sum(`是否遗漏`); |
4、我们的最终目标是求无遗漏率
|
|
select count(x.id) 无遗漏报告数,y.total 报告总数, concat(format(count(x.id)/y.total*100,2),'%') as 项目无遗漏率 from( select q.id,case when find_in_set(w.应添加项目列表,q.已添加项目列表)>0 then 1 else 0 end as 是否遗漏 from ( -- 报告已经添加的项目 select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 from report r left join report_item i on r.id=i.report_id where r.id in ('44930','44927') group by r.id )q, ( -- 报告应该添加的项目 select r.id,s.app_id,m.project_id 应添加项目列表 from report r inner join application a on r.app_id=a.id inner join application_sample s on a.id=s.app_id inner join application_sample_item si on s.id=si.sample_id inner join set_project_mapping m on si.set_id=m.set_id where r.id in ('44930','44927') order by r.id,m.project_id )w where q.id=w.id group by q.id having count(`是否遗漏`)=sum(`是否遗漏`) )x, ( -- 总报告数 select count(e.nums) as total from ( select count(r.id) as nums from report r where r.id in ('44930','44927') group by r.id )e )y ; |
方案二：
上述方案一虽然避免了逐行遍历对比，但本质上还是对项目的逐一对比，那么有没有什么方式可以不用对比呢？
答案当然是有的。我们可以根据统计数量判断是否完全包含。
1、使用union all 将已添加项目与应添加项目联表，不去重
|
|
( -- 应该添加的项目select r.id,m.project_idfrom report r inner join application a on r.app_id=a.idinner join application_sample s on a.id=s.app_idinner join application_sample_item si on s.id=si.sample_id inner join set_project_mapping m on si.set_id=m.set_idwhere r.id in ('44930','44927')order by r.id,m.project_id)union all( -- 已经添加的项目select r.id,i.project_id from report r,report_item i where r.id = i.report_id and r.id in ('44930','44927')group by r.app_id,i.project_id ) |
从结果可以看出，项目同一个报告下有重复的项目，分别代表了应该添加和已经添加的项目
2、根据联表结果，统计报告重合的项目数量
|
|
# 应该添加与已经添加的项目重叠数量select tt.id,count(*) count from( select t.id,t.project_id,count(*) from ( ( -- 应该添加的项目 select r.id,m.project_id from report r inner join application a on r.app_id=a.id inner join application_sample s on a.id=s.app_id inner join application_sample_item si on s.id=si.sample_id inner join set_project_mapping m on si.set_id=m.set_id where r.id in ('44930','44927') order by r.id,m.project_id ) union all ( -- 已经添加的项目 select r.id,i.project_id from report r,report_item i where r.id = i.report_id and r.id in ('44930','44927') group by r.app_id,i.project_id ) ) t group by t.id,t.project_id having count(*) >1 ) tt group by tt.id |
3、将第二步的数量与应该添加的数量作比较，如果相等，则代表无遗漏