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 (     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 ) aa right join (    -- 应该添加的项目数量    select      r.id,s.app_id, count (m.project_id) count    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' )    group by r.id    order by r.id,m.project_id ) bb on aa.id = bb.id order by aa.id

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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

select      sum (asr.`是否遗漏`) as 无遗漏数, count (asr.id) as 总数,concat(format( sum (asr.`是否遗漏`)/ count (asr.id)*100,5), '%' ) as 报告无遗漏率 from (    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    (       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    ) aa right join    (      -- 应该添加的项目数量      select        r.id,s.app_id, count (m.project_id) count      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' )      group by r.id      order by r.id,m.project_id    ) bb on aa.id = bb.id    order by aa.id ) asr;

到此这篇关于mysql 判断是否为子集的方法步骤的文章就介绍到这了,更多相关mysql 判断是否子集内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/kk_gods/article/details/112894187