我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

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

2022-05-12 19:21

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

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_id
WHERE 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_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将会导致截取不全，需要修改长度


SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

 从上述两个函数介绍中，我们发现FIND_IN_SET和GROUP_CONCAT都以英文‘,'分割（加粗标识）

所以，我们可以用GROUP_CONCAT将已添加项目的项目连接为一个字符串，然后再用FIND_IN_SET逐一查询应添加项目是否都存在于字符串

修改问题中描述中的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_id
WHERE r.id IN ('44930','44927')
GROUP BY r.id;

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

过滤掉有遗漏的报告


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

我们的最终目标是求无遗漏率


 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 
 ;

方案二：

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

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

使用union all 将已添加项目与应添加项目联表，不去重


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

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

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


# 应该添加与已经添加的项目重叠数量
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

将第二步的数量与应该添加的数量作比较，如果相等，则代表无遗漏


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

求出无遗漏率


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 判断是否子集内容请搜索自学编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学编程网！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

mysql判断是否子集mysql子集
阅读原文内容投诉

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

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

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

一、问题 故事起源于一个查询错漏率的报表：有两个查询结果，分别是报告已经添加的项目和报告应该添加的项目，求报告无遗漏率 何为无遗漏？即，应该添加的项目已经被全部添加 报告无遗漏率也就是无遗漏报告数占报告总数的比率 这里以两个报告示例（分别是
2022-05-12

判断List、Map集合是否为空的方法

2023-09-09

MySQL判断字段是否为null的方法

2024-04-02

java判断集合是否为空的方法是什么

在Java中，判断集合是否为空有几种方法可以使用：使用集合的isEmpty()方法：该方法返回一个boolean值，表示集合是否为空。例如：List list = new ArrayList();if(list.isE
java判断集合是否为空的方法是什么
2024-03-06

javascript判断是否为函数的方法

这篇文章将为大家详细讲解有关javascript判断是否为函数的方法，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。在javascript中，可以借助typeof和“===”操作符来判断是否为函数，语法格式
2023-06-14

python判断是否为整数的方法

这篇文章给大家分享的是有关python判断是否为整数的方法的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。python判断是否为整数的方法：1、使用【type()】函数判断，代码为【type(name, bases
2023-06-08

JavaScript判断是否为数字的方法

这篇文章主要讲解了“JavaScript判断是否为数字的方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“JavaScript判断是否为数字的方法”吧！JavaScript判断是否为数字的方
2023-06-14

javascript判断值是否为数字的方法

这篇“javascript判断值是否为数字的方法”除了程序员外大部分人都不太理解，今天小编为了让大家更加理解“javascript判断值是否为数字的方法”，给大家总结了以下内容，具有一定借鉴价值，内容详细步骤清晰，细节处理妥当，希望大家通过
2023-06-06

oracle判断是否为空的方法是什么

oracle判断是否为空的方法是什么
2024-04-02

java判断文件是否为空的方法

在File类中并没有提供判断文件是否为空的方法，但可以借助length()方法的返回值进行判断。（推荐：java视频教程）length()方法定义为：public long length()如果文件不存在或文件为空时，length()方法返回0。示例代码如下：
java判断文件是否为空的方法
2018-10-04

java判断string是否为数字的方法

java中判断字符串是否为数字的方法：1、用JAVA自带的函数public static boolean isNumeric(String str){for (int i = 0; i < str.length(); i++){System.out.print
java判断string是否为数字的方法
2022-01-16

java判断字符是否为数字的方法

java判断字符是否为数字的方法：（推荐：java视频教程）1、使用Character.isDigit(char)判断char num[] = str.toCharArray();//把字符串转换为字符数组StringBuffer title = new St
java判断字符是否为数字的方法
2016-02-23

java判断是否为整数的几种方法

方法一：用JAVA自带的函数public static boolean isNumeric(String str){ for (int i = str.length();--i>=0;){ if (!Character.isDigit(str
java判断是否为整数的几种方法
2015-08-30

java中判断文件是否为空的方法

在File类中并没有提供判断文件是否为空的方法，但可以借助length()方法的返回值进行判断。length()方法定义为：public long length()如果文件不存在或文件为空时，length()方法返回0。示例代码：File file = new
java中判断文件是否为空的方法
2020-12-30

oracle中判断是否为空的方法是什么

在Oracle中，可以使用以下方法判断是否为空：- 使用关键字IS NULL：可以使用IS NULL关键字来判断一个值是否为NULL。例如：SELECT * FROM table_name WHERE column_name IS NULL
2023-09-14

判断MYSQL服务是否正常的方法

2024-04-02

java判断是否为素数（质数）的方法

质数的定义：对于大于1的数，如果除了1和它本身，它不能再被其它正整数整除，那么我们说它是一个质数。判断一个数是否为质数（素数）方法：如果是偶数，直接返回；然后从3开始，步长为2，一直到n的算术平方根为止，都除不尽则为质数。Java程序：（推荐：java视频教程
java判断是否为素数（质数）的方法
2014-11-06

sql判断是否为数字的方法是什么

在SQL中，可以使用内置函数ISNUMERIC()来判断一个值是否为数字。该函数返回一个整数，如果值是数字，则返回1，否则返回0。示例如下：SELECT ISNUMERIC('123') AS IsNumeric; -
sql判断是否为数字的方法是什么
2024-04-09

java中判断数组是否为空的方法

一维数组需要根据数组长度判断，如果为0，则为空，反之不是；二维数组需要根据第一行的数组长度判断，如果为0，则为空，反之不是。实例如下：public class Main { public static void main(String[] args) {
java中判断数组是否为空的方法
2016-02-29

javascript判断是否为数字类型的方法

小编给大家分享一下javascript判断是否为数字类型的方法，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！方法：1、利用typeof关键字和isNaN()函数判断；2、利用typeof关键字和isFinite()函数判断
2023-06-14

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈