mysql返回结果集函数（mysql 判断是否为子集的方法步骤）

mysql返回结果集函数

mysql 判断是否为子集的方法步骤

目录

• 一、问题
• 二、解决方案
  • 方案一：
  • 方案二：

一、问题

故事起源于一个查询错漏率的报表：有两个查询结果，分别是报告已经添加的项目和报告应该添加的项目，求报告无遗漏率

何为无遗漏？即，应该添加的项目已经被全部添加

报告无遗漏率也就是无遗漏报告数占报告总数的比率

这里以两个报告示例（分别是已全部添加和有遗漏的报告）

首先，查出第一个结果——报告应该添加的项目

?

1 2 3 4 5 6 7 8 9 10

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_id where r.id in ('44930','44927') order by r.id,m.project_id;

然后，再查出第二个结果——报告已经添加的项目

?

1 2 3 4

select r.id as 报告id,i.project_id as 已添加项目 from report r right join report_item i on r.id=i.report_id where 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将会导致截取不全，需要修改长度

?

1 2	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将已添加项目的项目连接为一个字符串

?

1 2 3 4 5

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;

2、用find_in_set逐一查询应添加项目是否都存在于字符串

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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、过滤掉有遗漏的报告

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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、我们的最终目标是求无遗漏率

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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 将已添加项目与应添加项目联表，不去重

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

(  -- 应该添加的项目 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  )

从结果可以看出，项目同一个报告下有重复的项目，分别代表了应该添加和已经添加的项目

2、根据联表结果，统计报告重合的项目数量

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

# 应该添加与已经添加的项目重叠数量 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、将第二步的数量与应该添加的数量作比较，如果相等，则代表无遗漏

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

select bb.id,aa.count 已添加,bb.count 需添加,     case when aa.count/bb.count=1 then 1     else 0     end as '是否遗漏' from ( # 应该添加与已经添加的项目重叠数量 select tt.id,count(*) count from 标签： mysql 子集 上一篇下一篇 猜您喜欢 navicat不能连接到mysql报错2013（Navicat连接SQL Server数据：报错08001-命名管道提供程序的完美解决方法） mysql性能怎么看（是什么影响了 MySQL 的性能？） mysql剩余表空间大小（MySQL 表空间碎片的概念及相关问题解决） mysql慢日志查询作用（MySQL 慢查询日志的开启与配置） mysql完全快速清空一个表（浅谈MySQL如何优雅的做大表删除） mysql分组函数入门（详解MySQL 数据分组） navicat premium连接mysql报错（Navicat连接MySQL错误描述分析） docker部署mysql多实例（Docker部署mysql一主一从的操作方法） docker怎么创建mysql服务（Docker部署MySQL8集群一主二从的实现步骤） mysql的7种索引（浅入浅出 MySQL 索引） 春天养佛肚竹，做好这几件事，叶绿根壮寓意好 越养越旺家（春天养佛肚竹做好这几件事） 律界衣品最好，时尚圈学识数高，41岁的Amal堪称现实版的傲骨贤妻（时尚圈学识数高） 刘智泰确认出演《Good Wife》 担纲男一号（刘智泰确认出演Good） 《傲骨之战》剧终 律政女魔头Diane，为什么是大女主天花板（傲骨之战剧终律政女魔头Diane） 汉族（汉族的祖先是谁） 中国有几个名族（中国有几个民族没列入56个民族） 热门推荐 1JS中错误处理 2asp.net 消息队列 3香港云服务器租赁流程（租用香港云服务器的三大注意事项） 4python的log函数（Python3 log10函数简单用法） 5华为云服务器远程登录后怎么办（华为云服务器怎么登陆不上去） 6navicat如何连接服务器的数据库（Navicat如何远程连接云服务器数据库） 7docker指定容器id（Docker批量容器编排的实现） 8SQL Union和Union All的用法和区别 © 2021 开心学习