服务器之家

服务器之家 > 正文

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

时间：2021-04-12 16:34     来源/作者：看，月亮在跳舞

一、问题

 

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

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

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

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

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

?
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;

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

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

?
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');

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

以上就是我们要比较的结果集，不难看出报告44927是无遗漏的，而44930虽然项目数量一致，但实际是多添加了项目758，缺少了项目112，是有遗漏的报告

二、解决方案

 

从问题看，显然是一个判断是否为子集的问题。可以分别遍历已添加的项目和应该添加的项目，如果应该添加的项目在已添加的项目中都能匹配上，即代表应该添加的项目是已添加的项目子集，也就是无遗漏。

通过循环遍历比较确实可以解决这个问题，但是sql中出现笛卡儿积的交叉连接往往意味着开销巨大，查询速度慢，那么有没有办法避免这一问题呢？

方案一：

借助于函数 find_in_set和group_concat, 首先认识下两个函数

find_in_set(str,strlist)

  • str: 需要查询的字符串
  • strlist: 参数以英文”,”分隔，如 (1,2,6,8,10,22)

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

find_in_set 函数返回了需要查询的字符串在目标字符串的位置

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] )

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

group_concat()函数可以将多条记录的同一字段的值，拼接成一条记录返回。默认以英文‘,'分割。

但是，group_concat()默认长度为1024

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

所以，如果需要拼接的长度超过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;

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

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;

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

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(`是否遗漏`);

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

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
 ;

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

方案二：

上述方案一虽然避免了逐行遍历对比，但本质上还是对项目的逐一对比，那么有没有什么方式可以不用对比呢？

答案当然是有的。我们可以根据统计数量判断是否完全包含。

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
 )

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

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

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

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

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

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

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

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

标签：
mysql 子集 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
Intellij idea2020永久破解，亲测可用！！！
Intellij idea2020永久破解，亲测可用！！！ 2020-07-29
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部