当前位置:数据库 > > 正文

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

时间:2021-10-24 10:40:10类别:数据库

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;
  • mysql返回结果集函数(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返回结果集函数(mysql 判断是否为子集的方法步骤)

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

    二、解决方案

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

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

    方案一:

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

    find_in_set(str,strlist)

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

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

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

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

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

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

    mysql返回结果集函数(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返回结果集函数(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返回结果集函数(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返回结果集函数(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返回结果集函数(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返回结果集函数(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返回结果集函数(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
  • 标签:

    猜您喜欢