故事起源于一個(gè)查詢錯(cuò)漏率的報(bào)表：有兩個(gè)查詢結(jié)果，分別是報(bào)告已經(jīng)添加的項(xiàng)目和報(bào)告應(yīng)該添加的項(xiàng)目，求報(bào)告無遺漏率

何為無遺漏？即，應(yīng)該添加的項(xiàng)目已經(jīng)被全部添加

報(bào)告無遺漏率也就是無遺漏報(bào)告數(shù)占報(bào)告總數(shù)的比率

這里以兩個(gè)報(bào)告示例（分別是已全部添加和有遺漏的報(bào)告）

首先，查出第一個(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_idWHERE r.id IN (’44930’,’44927’)ORDER BY r.id,m.project_id;

然后，再查出第二個(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_idWHERE r.id IN (’44930’,’44927’);

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

從問題看，顯然是一個(gè)判斷是否為子集的問題。可以分別遍歷已添加的項(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)識(shí)下兩個(gè)函數(shù)

FIND_IN_SET(str,strlist)

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

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

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

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

所以，如果需要拼接的長度超過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)識(shí)）

所以，我們可以用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_idWHERE r.id IN (’44930’,’44927’)GROUP BY r.id;

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_idINNER 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、過濾掉有遺漏的報(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_idINNER 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、我們的最終目標(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_idINNER 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 ;

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

答案當(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_idFROM report r INNER JOIN application a ON r.app_id=a.idINNER JOIN application_sample s ON a.id=s.app_idINNER JOIN application_sample_item si ON s.id=si.sample_id INNER JOIN set_project_mapping m ON si.set_id=m.set_idWHERE 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 )

從結(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_idFROM 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_idWHERE 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

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_idFROM 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_idWHERE 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

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;

到此這篇關(guān)于mysql 判斷是否為子集的方法步驟的文章就介紹到這了,更多相關(guān)mysql 判斷是否子集內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！