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

2024-07-31 11:07:19

一、问题

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

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

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

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

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

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逐一查询应添加项目是否都存在于字符串

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

MySql8 WITH RECURSIVE递归查询父子集的方法

背景开发过程中遇到类似评论的功能是,需要时用查询所有评论的子集.不同数据库中实现方式也不同,本文使用Mysql数据库,版本为8.0 Oracle数据库中可使用START [Param] CONNECT BY PRIOR Mysql 中需要使用 WITH RECURSIVE 需求找到name为张三的孩子和孙子,pid为当前记录的父id,如张三儿子的pid为张三的id,以此类推. 引入计算1到100的累加的结果. WITH RECURSIVE t(n) AS ( //t为我们结果表,n为字段,
mysql 判断是否为子集的方法步骤

一.问题故事起源于一个查询错漏率的报表:有两个查询结果,分别是报告已经添加的项目和报告应该添加的项目,求报告无遗漏率何为无遗漏?即,应该添加的项目已经被全部添加报告无遗漏率也就是无遗漏报告数占报告总数的比率这里以两个报告示例(分别是已全部添加和有遗漏的报告) 首先,查出第一个结果--报告应该添加的项目 SELECT r.id AS 报告ID,m.project_id 应添加项目 FROM report r INNER JOIN application a ON r.app_id=a.id
Mysql 查询数据库容量大小的方法步骤

查询所有数据库的总大小方法如下: mysql> use information_schema; mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES; +-----------+ | data | +-----------+ | 3052.76MB | +-----------+ 1 row in set (0.02 sec) 统计一下所有库数据量每张表数据量=AVG_ROW_L
mysql定时自动备份数据库的方法步骤

我们都知道数据是无价,如果不对数据进行备份,相当是让数据在裸跑,一旦服务器出问题,只有哭的份了.下面将介绍mysql数据库的自动备份,大家可根据情况自行选择将备份数据存放在哪里. 第一种,数据跑在服务器上,如果只想定时把数据库备份放在服务器(此处服务器系统为centos7)上,可如下设置: 首先编写sh文件 : /usr/local/backup/bkDatabase.sh: #!/bin/bash mysqldump -uusername -ppassword DatabaseName | g
Win7、WinXP下MySql安装出错完全卸载的方法步骤

最近mysql安装出现问题: 1.操作系统不同,安装过程中删除,清理也不同: window 7: 安装mysql过程中,最后安装到start service的时候提示服务有问题,然后卸载,卸载后按照还是同样的错误,下面就说卸载过程如何全面卸载干净: 1)找到mysql安装路径,目前默认c盘,然后找到组织菜单----文件夹和搜索选项-----查看tab中,有个显示隐藏的文件,文件夹和驱动器,勾选后,然后再c盘下面有个programdata文件夹,删除此文件夹下面的mysql文件夹. 2)找到控制面
mysql数据库设置utf-8编码的方法步骤

修改/etc/my.cnf或者/etc/mysql/my.cnf文件 [client] default-character-set = utf8 [mysqld] default-storage-engine = INNODB character-set-server = utf8 collation-server = utf8_general_ci 重启mysql,使用mysql客户端检查编码 show variables like '%char%'; 创建新数据库时使用UTF-8编码 cre
mysql判断字段是否存在的方法

1. desc 命令格式: desc tablename columnname 例子: desc `table` `mid` desc `table` '%abc%' 2. show columns 命令格式: show columns from tablename like columnname 例子: show columns from `table` like 'mid' show columns from `table` like '%abc%' 3. describe 命令格式:
PHP判断一个数组是另一个数组子集的方法详解

本文实例讲述了PHP判断一个数组是另一个数组子集的方法.分享给大家供大家参考,具体如下: 前言今天完成一个算法的过程中,有几个需求模块,其中就有判断$a数组是否是$b数组的子集,可能最近我写c比较多,直接就用for循环实现了,但是感觉代码量比较大,不够优雅!在qq群里集思广益了一下,发现很多php提供的系统功能函数都是可以供调用的,这里记录一下需求最少的时间复杂度判断$a数组是否是$b数组的子集 // 快速的判断$a数组是否是$b数组的子集 $a = array(135,138); $b
Mysql双主搭建的方法步骤

目录 1. Mysql binlog参数配置 2. Mysql binlog查看详细内容 3. Mysql双主搭建 4. Mysql双主解决数据回环 4.1 双主同步测试一 4.2 双主同步测试二 4.3 双主同步测试三 1. Mysql binlog参数配置 log-bin=mysql-bin 打开二进制日志功能,默认在datadir下 binlog-ignore-db binlog-ignore-db=mysql binlog-ignore-db=information_schema bin
mysql 5.7.20解压版安装方法步骤详解(两种方法)

我来讲解下window64位下MySQL的安装,MySQL是在5.7开始安装版就只有32位下载服务了,这里我讲解解压版的MySQL如何安装,在安装MySQL解压版时对于新手的小编来说也是头疼得很,各种问题各种来没有安装版的一键轻松搞定的方便,安装时需要注意三点:1.路径配置,2.安装时MySQL端口被占用这时需要关闭被占用端口,3.cmd必须是在管理员环境下设置MySQL信息. MySQL官网: https://www.mysql.com/downloads/ http://www.jb51.n
php+MySQL判断update语句是否执行成功的方法

update语句是PHP+MySQL中常用的操作,判断update语句是否执行成功是其中非常重要的一个环节.本文就以实例展示了php+MySQL判断update语句是否执行成功的方法.分享给大家供大家参考之用.具体方法如下: 代码一: $rs=MySQL_query($sql); if(mysql_affected_rows()) echo "sql执行成功"; else echo "sql执行失败"; 代码二: <?php /* 连接数据库 */ mysql

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

一、问题

二、解决方案

方案一：

方案二：

相关推荐

随机推荐