溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

如何在mysql 中對子集進(jìn)行判斷

發(fā)布時(shí)間：2021-02-07 19:19:29 來源：億速云 閱讀：285 作者：Leah 欄目：開發(fā)技術(shù)

這篇文章將為大家詳細(xì)講解有關(guān)如何在mysql 中對子集進(jìn)行判斷，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

首先，查出第一個(gè)結(jié)果——報(bào)告應(yīng)該添加的項(xiàng)目

SELECT 
     r.id AS 報(bào)告ID,m.project_id 應(yīng)添加項(xiàng)目
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 中對子集進(jìn)行判斷

然后，再查出第二個(gè)結(jié)果——報(bào)告已經(jīng)添加的項(xiàng)目

SELECT r.id AS 報(bào)告ID,i.project_id AS 已添加項(xiàng)目 
FROM report r 
RIGHT JOIN report_item i ON r.id=i.report_id
WHERE r.id IN ('44930','44927');

如何在mysql 中對子集進(jìn)行判斷

以上就是我們要比較的結(jié)果集，不難看出報(bào)告44927是無遺漏的，而44930雖然項(xiàng)目數(shù)量一致，但實(shí)際是多添加了項(xiàng)目758，缺少了項(xiàng)目112，是有遺漏的報(bào)告

二、解決方案

從問題看，顯然是一個(gè)判斷是否為子集的問題?？梢苑謩e遍歷已添加的項(xiàng)目和應(yīng)該添加的項(xiàng)目，如果應(yīng)該添加的項(xiàng)目在已添加的項(xiàng)目中都能匹配上，即代表應(yīng)該添加的項(xiàng)目是已添加的項(xiàng)目子集，也就是無遺漏。

通過循環(huán)遍歷比較確實(shí)可以解決這個(gè)問題，但是SQL中出現(xiàn)笛卡兒積的交叉連接往往意味著開銷巨大，查詢速度慢，那么有沒有辦法避免這一問題呢？

方案一：

借助于函數(shù) FIND_IN_SET和GROUP_CONCAT, 首先認(rèn)識下兩個(gè)函數(shù)

FIND_IN_SET(str,strlist)

  • str: 需要查詢的字符串

  • strlist: 參數(shù)以英文”,”分隔，如 (1,2,6,8,10,22)

如何在mysql 中對子集進(jìn)行判斷

FIND_IN_SET 函數(shù)返回了需要查詢的字符串在目標(biāo)字符串的位置

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

如何在mysql 中對子集進(jìn)行判斷

GROUP_CONCAT()函數(shù)可以將多條記錄的同一字段的值，拼接成一條記錄返回。默認(rèn)以英文‘,'分割。

但是，GROUP_CONCAT()默認(rèn)長度為1024

如何在mysql 中對子集進(jìn)行判斷

所以，如果需要拼接的長度超過1024將會(huì)導(dǎo)致截取不全，需要修改長度

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

 從上述兩個(gè)函數(shù)介紹中，我們發(fā)現(xiàn)FIND_IN_SET和GROUP_CONCAT都以英文‘,'分割（加粗標(biāo)識）

所以，我們可以用GROUP_CONCAT將已添加項(xiàng)目的項(xiàng)目連接為一個(gè)字符串，然后再用FIND_IN_SET逐一查詢應(yīng)添加項(xiàng)目是否都存在于字符串

1、修改問題中描述中的SQL，用GROUP_CONCAT將已添加項(xiàng)目的項(xiàng)目連接為一個(gè)字符串

SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS 已添加項(xiàng)目列表 
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 中對子集進(jìn)行判斷

2、用FIND_IN_SET逐一查詢應(yīng)添加項(xiàng)目是否都存在于字符串

SELECT Q.id,FIND_IN_SET(W.應(yīng)添加項(xiàng)目列表,Q.已添加項(xiàng)目列表) AS 是否遺漏
   FROM 
   (
   -- 報(bào)告已經(jīng)添加的項(xiàng)目 
      SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS 已添加項(xiàng)目列表 
      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,
   (
   -- 報(bào)告應(yīng)該添加的項(xiàng)目 
      SELECT 
         r.id,s.app_id,m.project_id 應(yīng)添加項(xiàng)目列表
      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 中對子集進(jìn)行判斷

3、過濾掉有遺漏的報(bào)告

 SELECT Q.id,CASE WHEN FIND_IN_SET(W.應(yīng)添加項(xiàng)目列表,Q.已添加項(xiàng)目列表)>0 THEN 1 ELSE 0 END AS 是否遺漏
   FROM 
   (
   -- 報(bào)告已經(jīng)添加的項(xiàng)目 
      SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS 已添加項(xiàng)目列表 
      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,
   (
   -- 報(bào)告應(yīng)該添加的項(xiàng)目 
      SELECT 
         r.id,s.app_id,m.project_id 應(yīng)添加項(xiàng)目列表
      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 中對子集進(jìn)行判斷

4、我們的最終目標(biāo)是求無遺漏率

 SELECT COUNT(X.id) 無遺漏報(bào)告數(shù),Y.total 報(bào)告總數(shù), CONCAT(FORMAT(COUNT(X.id)/Y.total*100,2),'%') AS 項(xiàng)目無遺漏率 FROM 
(
  SELECT Q.id,CASE WHEN FIND_IN_SET(W.應(yīng)添加項(xiàng)目列表,Q.已添加項(xiàng)目列表)>0 THEN 1 ELSE 0 END AS 是否遺漏
   FROM 
   (
   -- 報(bào)告已經(jīng)添加的項(xiàng)目 
      SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS 已添加項(xiàng)目列表 
      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,
   (
   -- 報(bào)告應(yīng)該添加的項(xiàng)目 
      SELECT 
         r.id,s.app_id,m.project_id 應(yīng)添加項(xiàng)目列表
       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,
 (
    -- 總報(bào)告數(shù)
    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 中對子集進(jìn)行判斷

方案二：

上述方案一雖然避免了逐行遍歷對比，但本質(zhì)上還是對項(xiàng)目的逐一對比，那么有沒有什么方式可以不用對比呢？

答案當(dāng)然是有的。我們可以根據(jù)統(tǒng)計(jì)數(shù)量判斷是否完全包含。

1、使用union all 將已添加項(xiàng)目與應(yīng)添加項(xiàng)目聯(lián)表，不去重

 (
 -- 應(yīng)該添加的項(xiàng)目
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
(
 -- 已經(jīng)添加的項(xiàng)目
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 中對子集進(jìn)行判斷

從結(jié)果可以看出，項(xiàng)目同一個(gè)報(bào)告下有重復(fù)的項(xiàng)目，分別代表了應(yīng)該添加和已經(jīng)添加的項(xiàng)目

2、根據(jù)聯(lián)表結(jié)果，統(tǒng)計(jì)報(bào)告重合的項(xiàng)目數(shù)量

# 應(yīng)該添加與已經(jīng)添加的項(xiàng)目重疊數(shù)量
select tt.id,count(*) count from 
(
   select t.id,t.project_id,count(*) from 
   (
      (
        -- 應(yīng)該添加的項(xiàng)目
        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
      (
        -- 已經(jīng)添加的項(xiàng)目
        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 中對子集進(jìn)行判斷

3、將第二步的數(shù)量與應(yīng)該添加的數(shù)量作比較，如果相等，則代表無遺漏

select bb.id,aa.count 已添加,bb.count 需添加,
    CASE WHEN aa.count/bb.count=1 THEN 1
    ELSE 0
    END AS '是否遺漏' 
from 
(
# 應(yīng)該添加與已經(jīng)添加的項(xiàng)目重疊數(shù)量
select tt.id,count(*) count from 
(
   select t.id,t.project_id,count(*) from 
   (
      (
        -- 應(yīng)該添加的項(xiàng)目
        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
      (
        -- 已經(jīng)添加的項(xiàng)目
        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
(
  -- 應(yīng)該添加的項(xiàng)目數(shù)量
  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 中對子集進(jìn)行判斷

4、求出無遺漏率

select 
    SUM(asr.`是否遺漏`) AS 無遺漏數(shù),COUNT(asr.id) AS 總數(shù),CONCAT(FORMAT(SUM(asr.`是否遺漏`)/COUNT(asr.id)*100,5),'%') AS 報(bào)告無遺漏率
from 
(
  select bb.id,aa.count 已添加,bb.count 需添加,
      CASE WHEN aa.count/bb.count=1 THEN 1
      ELSE 0
      END AS '是否遺漏' 
  from 
  (
  # 應(yīng)該添加與已經(jīng)添加的項(xiàng)目重疊數(shù)量
  select tt.id,count(*) count from 
  (
     select t.id,t.project_id,count(*) from 
     (
        (
          -- 應(yīng)該添加的項(xiàng)目
          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
        (
          -- 已經(jīng)添加的項(xiàng)目
          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
  (
    -- 應(yīng)該添加的項(xiàng)目數(shù)量
    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 中對子集進(jìn)行判斷

關(guān)于如何在mysql 中對子集進(jìn)行判斷就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手