登录注册

当前位置：首页 > 文章列表 > 数据库 > MySQL > mysql 判断是否为子集的方法步骤

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

来源：脚本之家 2022-12-28 15:58:48 0浏览 收藏

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《mysql 判断是否为子集的方法步骤》，主要介绍了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');

二、解决方案

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

通过循环遍历比较确实可以解决这个问题，但是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逐一查询应添加项目是否都存在于字符串

1、修改问题中描述中的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;

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

3、过滤掉有遗漏的报告

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

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

 (
 -- 应该添加的项目
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、根据联表结果，统计报告重合的项目数量

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

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、求出无遗漏率

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

今天关于《mysql 判断是否为子集的方法步骤》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于mysql的内容请关注golang学习网公众号！

mysql mysql子集
版本声明
本文转载于：脚本之家 如有侵犯，请联系study_golang@163.com删除
mysql 大表批量删除大量数据的实现方法mysql 大表批量删除大量数据的实现方法
上一篇
mysql 大表批量删除大量数据的实现方法
mysql 数据类型转换的实现
下一篇
mysql 数据类型转换的实现
评论列表
查看更多
最新文章
查看更多
课程推荐
  • 前端进阶之JavaScript设计模式
    前端进阶之JavaScript设计模式
    设计模式是开发人员在软件开发过程中面临一般问题时的解决方案，代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景，打造一站式知识长龙服务，适合有JS基础的同学学习。
    542次学习
  • GO语言核心编程课程
    GO语言核心编程课程
    本课程采用真实案例，全面具体可落地，从理论到实践，一步一步将GO核心编程技术、编程思想、底层实现融会贯通，使学习者贴近时代脉搏，做IT互联网时代的弄潮儿。
    508次学习
  • 简单聊聊mysql8与网络通信
    简单聊聊mysql8与网络通信
    如有问题加微信：Le-studyg；在课程中，我们将首先介绍MySQL8的新特性，包括性能优化、安全增强、新数据类型等，帮助学生快速熟悉MySQL8的最新功能。接着，我们将深入解析MySQL的网络通信机制，包括协议、连接管理、数据传输等，让
    497次学习
  • JavaScript正则表达式基础与实战
    JavaScript正则表达式基础与实战
    在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
    487次学习
  • 从零制作响应式网站—Grid布局
    从零制作响应式网站—Grid布局
    本系列教程将展示从零制作一个假想的网络科技公司官网，分为导航，轮播，关于我们，成功案例，服务流程，团队介绍，数据部分，公司动态，底部信息等内容区块。网站整体采用CSSGrid布局，支持响应式，有流畅过渡和展现动画。
    484次学习
查看更多
AI推荐
  • SEO标题魔匠AI：高质量学术写作平台，毕业论文生成与优化专家
    魔匠AI
    SEO摘要魔匠AI专注于高质量AI学术写作，已稳定运行6年。提供无限改稿、选题优化、大纲生成、多语言支持、真实参考文献、数据图表生成、查重降重等全流程服务，确保论文质量与隐私安全。适用于专科、本科、硕士学生及研究者，满足多语言学术需求。
    14次使用
  • PPTFake答辩PPT生成器：一键生成高效专业的答辩PPT
    PPTFake答辩PPT生成器
    PPTFake答辩PPT生成器，专为答辩准备设计，极致高效生成PPT与自述稿。智能解析内容，提供多样模板，数据可视化，贴心配套服务，灵活自主编辑，降低制作门槛，适用于各类答辩场景。
    29次使用
  • SEO标题Lovart AI：全球首个设计领域AI智能体，实现全链路设计自动化
    Lovart
    SEO摘要探索Lovart AI，这款专注于设计领域的AI智能体，通过多模态模型集成和智能任务拆解，实现全链路设计自动化。无论是品牌全案设计、广告与视频制作，还是文创内容创作，Lovart AI都能满足您的需求，提升设计效率，降低成本。
    27次使用
  • 美图AI抠图：行业领先的智能图像处理技术，3秒出图，精准无误
    美图AI抠图
    美图AI抠图，依托CVPR 2024竞赛亚军技术，提供顶尖的图像处理解决方案。适用于证件照、商品、毛发等多场景，支持批量处理，3秒出图，零PS基础也能轻松操作，满足个人与商业需求。
    35次使用
  • SEO标题PetGPT：智能桌面宠物程序，结合AI对话的个性化陪伴工具
    PetGPT
    SEO摘要PetGPT 是一款基于 Python 和 PyQt 开发的智能桌面宠物程序，集成了 OpenAI 的 GPT 模型，提供上下文感知对话和主动聊天功能。用户可高度自定义宠物的外观和行为，支持插件热更新和二次开发。适用于需要陪伴和效率辅助的办公族、学生及 AI 技术爱好者。
    36次使用
查看更多
相关文章
关于我们 免责声明 意见反馈 联系我们 内容提交
Golang学习网：公益在线Go学习平台，帮助Go学习者快速成长！
技术交流群
Copyright 2023 http://www.17golang.com/ All Rights Reserved ｜ 苏ICP备2023003363号-1
  • Golang学习网
    关注公众号
    Golang学习网
微信登录更方便
  • 密码登录
  • 注册账号
忘记密码
登录即同意 用户协议 和 隐私政策
返回登录
  • 重置密码
发送验证码