mysql 判斷是否為子集的方法步驟
一、問(wèn)題
故事起源于一個(gè)查詢(xún)錯(cuò)漏率的報(bào)表：有兩個(gè)查詢(xún)結(jié)果，分別是報(bào)告已經(jīng)添加的項(xiàng)目和報(bào)告應(yīng)該添加的項(xiàng)目，求報(bào)告無(wú)遺漏率
何為無(wú)遺漏？即，應(yīng)該添加的項(xiàng)目已經(jīng)被全部添加
報(bào)告無(wú)遺漏率也就是無(wú)遺漏報(bào)告數(shù)占報(bào)告總數(shù)的比率
這里以?xún)蓚€(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_id
WHERE 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_id
WHERE r.id IN ('44930','44927');
以上就是我們要比較的結(jié)果集，不難看出報(bào)告44927是無(wú)遺漏的，而44930雖然項(xiàng)目數(shù)量一致，但實(shí)際是多添加了項(xiàng)目758，缺少了項(xiàng)目112，是有遺漏的報(bào)告
二、解決方案
從問(wèn)題看，顯然是一個(gè)判斷是否為子集的問(wèn)題。可以分別遍歷已添加的項(xiàng)目和應(yīng)該添加的項(xiàng)目，如果應(yīng)該添加的項(xiàng)目在已添加的項(xiàng)目中都能匹配上，即代表應(yīng)該添加的項(xiàng)目是已添加的項(xiàng)目子集，也就是無(wú)遺漏。
通過(guò)循環(huán)遍歷比較確實(shí)可以解決這個(gè)問(wèn)題，但是SQL中出現(xiàn)笛卡兒積的交叉連接往往意味著開(kāi)銷(xiāo)巨大，查詢(xún)速度慢，那么有沒(méi)有辦法避免這一問(wèn)題呢？
方案一：
借助于函數(shù) FIND_IN_SET和GROUP_CONCAT, 首先認(rèn)識(shí)下兩個(gè)函數(shù)
FIND_IN_SET(str,strlist)
- str: 需要查詢(xún)的字符串
- strlist: 參數(shù)以英文”,”分隔，如 (1,2,6,8,10,22)
FIND_IN_SET 函數(shù)返回了需要查詢(xún)的字符串在目標(biāo)字符串的位置
GROUP_CONCAT( [distinct] 要連接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
GROUP_CONCAT()函數(shù)可以將多條記錄的同一字段的值，拼接成一條記錄返回。默認(rèn)以英文‘,'分割。
但是，GROUP_CONCAT()默認(rèn)長(zhǎng)度為1024
所以，如果需要拼接的長(zhǎng)度超過(guò)1024將會(huì)導(dǎo)致截取不全，需要修改長(zhǎng)度
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逐一查詢(xún)應(yīng)添加項(xiàng)目是否都存在于字符串
1、修改問(wèn)題中描述中的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;
2、用FIND_IN_SET逐一查詢(xún)應(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;
3、過(guò)濾掉有遺漏的報(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(`是否遺漏`);
4、我們的最終目標(biāo)是求無(wú)遺漏率
SELECT COUNT(X.id) 無(wú)遺漏報(bào)告數(shù),Y.total 報(bào)告總數(shù), CONCAT(FORMAT(COUNT(X.id)/Y.total*100,2),'%') AS 項(xiàng)目無(wú)遺漏率 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
;
方案二：
上述方案一雖然避免了逐行遍歷對(duì)比，但本質(zhì)上還是對(duì)項(xiàng)目的逐一對(duì)比，那么有沒(méi)有什么方式可以不用對(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_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
)
從結(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
3、將第二步的數(shù)量與應(yīng)該添加的數(shù)量作比較，如果相等，則代表無(wú)遺漏
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
4、求出無(wú)遺漏率
select
SUM(asr.`是否遺漏`) AS 無(wú)遺漏數(shù),COUNT(asr.id) AS 總數(shù),CONCAT(FORMAT(SUM(asr.`是否遺漏`)/COUNT(asr.id)*100,5),'%') AS 報(bào)告無(wú)遺漏率
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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle是大型數(shù)據(jù)庫(kù)而Mysql是中小型數(shù)據(jù)庫(kù)，Oracle市場(chǎng)占有率達(dá)40%，Mysql只有20%左右，同時(shí)Mysql是開(kāi)源的而Oracle價(jià)格非常高2017-06-06
這篇文章主要介紹了MySQL之索引結(jié)構(gòu)解讀，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
MySQL(基于GTID方式)實(shí)現(xiàn)主從復(fù)制和單主復(fù)制詳細(xì)教程
在分布式數(shù)據(jù)庫(kù)系統(tǒng)中,主從復(fù)制是實(shí)現(xiàn)高可用性和數(shù)據(jù)冗余的重要手段,基于GTID的復(fù)制模式可以提供更強(qiáng)的復(fù)制一致性和簡(jiǎn)化故障轉(zhuǎn)移過(guò)程,本文將詳細(xì)介紹如何配置單主復(fù)制的GTID模式,以便在MySQL數(shù)據(jù)庫(kù)中實(shí)現(xiàn)穩(wěn)定可靠的數(shù)據(jù)復(fù)制,需要的朋友可以參考下2024-07-07
mysql創(chuàng)建學(xué)生表、課程表及學(xué)生選課表詳細(xì)代碼
這篇文章主要給大家介紹了mysql創(chuàng)建學(xué)生表、課程表及學(xué)生選課表的相關(guān)資料,學(xué)生、課程以及成績(jī)的增刪改查都是建立在連接數(shù)據(jù)庫(kù)的基礎(chǔ)之上,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-12-12
mysql數(shù)據(jù)庫(kù)遠(yuǎn)程訪問(wèn)設(shè)置方法
MySQL數(shù)據(jù)庫(kù)不允許從遠(yuǎn)程訪問(wèn)怎么辦?本文提供了三種解決方法，需要的朋友可以參考下2008-02-02
mysql下的max_allowed_packet參數(shù)設(shè)置詳解
本文主要介紹了mysql下的max_allowed_packet參數(shù)設(shè)置詳解，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-02-02
在SQL中修改數(shù)據(jù)的基礎(chǔ)語(yǔ)句
修改數(shù)據(jù)SQL中,可以使用UPDATE語(yǔ)句來(lái)修改、更新一個(gè)或多個(gè)表的數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于在SQL中修改數(shù)據(jù)的基礎(chǔ)語(yǔ)句,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02
mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識(shí)點(diǎn)總結(jié)
這篇文章主要介紹了mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識(shí)點(diǎn),結(jié)合實(shí)例形式總結(jié)分析了mysql中關(guān)于null的判斷、使用相關(guān)操作技巧與注意事項(xiàng),需要的朋友可以參考下2019-12-12
SQL Server索引設(shè)計(jì)基礎(chǔ)知識(shí)詳解使用
為了使索引的使用效率更高，在創(chuàng)建索引時(shí)，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類(lèi)型的索引。索引設(shè)計(jì)不合理或者缺少索引都會(huì)對(duì)數(shù)據(jù)庫(kù)和應(yīng)用程序的性能造成障礙。高效的索引對(duì)于獲得良好的性能非常重要。設(shè)計(jì)索引時(shí)，應(yīng)該考慮相應(yīng)準(zhǔn)則2023-04-04
MySQL 數(shù)據(jù)庫(kù)兩臺(tái)主機(jī)同步實(shí)戰(zhàn)(linux)
MySQL支持單向、異步復(fù)制，復(fù)制過(guò)程中一個(gè)服務(wù)器充當(dāng)主服務(wù)器，而一個(gè)或多個(gè)其它服務(wù)器充當(dāng)從服務(wù)器。主服務(wù)器將更新寫(xiě)入二進(jìn)制日志文件，并維護(hù)日志文件的一個(gè)索引以跟蹤日志循環(huán)。2009-04-04