教育新聞網(wǎng)
大家好，今日我們來聊聊一篇關(guān)于笛卡爾積，SQL查詢中笛卡爾積的巧妙使用的文章，希望對大家有所幫助
笛卡爾積(在SQL查詢中巧妙使用笛卡爾積)
本文用兩個(gè)小例子來學(xué)習(xí)笛卡爾積的巧妙運(yùn)用。后臺(tái)回復(fù)“笛卡爾產(chǎn)品”即可獲得本文pdf版本，方便閱讀和保存。
笛卡爾積，也稱為交叉連接，是SQL中連接兩個(gè)表的一種方式。
如果表A的數(shù)據(jù)是M行，表B的數(shù)據(jù)是N行，那么A和B做笛卡爾積，結(jié)果是m*n行。
笛卡爾乘積是這樣寫的：
Select*fromA，B或select*fromAcrossjoinB通常情況下，我們應(yīng)該避免在實(shí)際的SQL中直接使用笛卡爾積，因?yàn)檫@樣會(huì)造成“數(shù)據(jù)爆炸”，尤其是在數(shù)據(jù)量很大的時(shí)候。但有時(shí)候，巧妙利用笛卡爾積可以幫助我們快速解決實(shí)際問題。看看下面的例子。
在此之前，我們先來看看with as的用法。
使用tmpa(select * from class)select * from tmp。上面的編寫方法首先通過執(zhí)行select * from class定義(生成)一個(gè)中間表tmp，然后使用中間表tmp。通常可以用來提取固定的查詢，只檢查一次，多次使用，從而提高效率。它還可以與union all結(jié)合來構(gòu)造測試數(shù)據(jù)，我們將在本文下一部分的后面的場景中看到這種用法。關(guān)于as的一些要點(diǎn)和注意事項(xiàng)，請參考以下鏈接：
https://blog.csdn.net/baidu_30527569/article/details/48680745
假設(shè)有一張損益表，每過一個(gè)小時(shí)，就會(huì)自動(dòng)更新前一個(gè)小時(shí)的收入數(shù)據(jù)。但是，對于沒有更新的時(shí)間，我們希望收入值為0。這樣可以更好的體現(xiàn)完整性，也便于多日數(shù)據(jù)的對比。如下圖所示：
對于收益非零的小時(shí)，我們可以直接從收益表中查詢當(dāng)前小時(shí)的收益數(shù)據(jù)。收益表結(jié)構(gòu)如下(假設(shè)當(dāng)前收益數(shù)據(jù)只更新到16點(diǎn)):
查詢SQL是：
DT，HOUR，income fromt _ H _ income whereday=' 2020-04-19 '顯然，得到的結(jié)果不會(huì)包括17點(diǎn)以后的時(shí)間。我們可以使用笛卡爾積來構(gòu)造一個(gè)小時(shí)序列，如下面的代碼所示：
with t _ houras(select ' 00 ' asdhourunionallselect ' 01 ' asdhourunionallselect ' 02 ' asdhourunionallselect ' 03 ' asdhourunionallselect ' 04 ' asdhourunionallselect ' 05 ' ASD hourinoniallselect ' 07 ' asdhourunionallselect ' 08 ' asdhourunionallselect ' 09 ' asdhourunionallselect ' 10 ' asdhourunionallselect ' 11 ' asdh ourunionallall
將上面的結(jié)果與左邊的原始數(shù)據(jù)相關(guān)聯(lián)，將未關(guān)聯(lián)的結(jié)果設(shè)置為0，以獲得所需的結(jié)果。代碼如下：
with t _ houras(select ' 00 ' asdhourunionallselect ' 01 ' asdhourunionallselect ' 02 ' asdhourunionallselect ' 03 ' asdhourunionallselect ' 04 ' asdhourunionallselect ' 05 ' ASD houriunionallselect ' 06 ' asdhourunionallselect ' 07 ' asdhouru
通過手動(dòng)構(gòu)造dt和dhour，用笛卡爾積產(chǎn)生了一個(gè)“序列”。而對于dhour的構(gòu)造，也可以采用笛卡爾積的方式，但需要注意限制范圍不大于23，代碼如下：
witht_houras(select深圳生活網(wǎng)9;0'asidunionallselect'1'asidunionallselect'2'asid),f_houras(select'0'asidunionallselect'1'asidunionallselect'2'asidunionallselect'3'asidunionallselect'4'asidunionallselect'5'asidunionallselect'6'asidunionallselect'7'asidunionallselect'8'asidunionallselect'9'asid)selectconcat(a.id,b.id)hourfromt_houra,f_hourbwhereconcat(a.id,b.id)<='23'orderbyhour
以上我們都主要使用了笛卡爾積產(chǎn)生順序值的場景，類似的可以構(gòu)造從00~99的數(shù)字，構(gòu)造之后也可以根據(jù)實(shí)際需要加入新的限制條件。
注：例子來源于《SQL Cookbook》第6章，經(jīng)過自己的修改。
問題：考慮用SQL實(shí)現(xiàn)：將表emp中name為KING的字符串顯示為4行，每行包含其中一個(gè)字符。
這里需要笛卡爾積配合字符串截取函數(shù)來實(shí)現(xiàn)。要實(shí)現(xiàn)逐一訪問字符串，需要有一個(gè)中間表，存儲(chǔ)序列值，類似于前面提到的序列。我們看下下面的代碼：
witht5as(select1asposunionallselect2asposunionallselect3asposunionallselect4asposunionallselect5aspos),empas(select'KING'asname)select*fromemp,t5
得到的結(jié)果如下圖所示：
考慮到字符串截取函數(shù)能夠按位置截取。正好可以用上生成的pos。代碼如下:
witht5as(select1asposunionallselect2asposunionallselect3asposunionallselect4asposunionallselect5aspos),empas(select'KING'asname)selectsubstr(name,pos,1)fromemp,t5wheret5.pos<=length(emp.name)
可以看到使用了pos，就能夠“循環(huán)”地截取字符串了。需要注意where里加上了循環(huán)跳出的條件，這也比較好理解：不能截取超過字符串長度的字符。
還可以按照需要調(diào)整遍歷時(shí)輸出的格式，如下面代碼和結(jié)果所示：
selectsubstr(name,pos)char_name1,substr(name,length(name)-pos+1)char_name1fromemp,t5wheret5.pos<=length(emp.name)
這個(gè)例子中我們利用笛卡爾積模擬循環(huán)，對字符串進(jìn)行了遍歷。
本文首先學(xué)習(xí)了with as的用法，然后通過例子總結(jié)了兩個(gè)巧妙使用笛卡爾積的場景：生成序列和模擬循環(huán)。雖然在實(shí)際中可能用的不是很多，但也體現(xiàn)出了SQL的靈活性。生成序列可以更廣義的理解為：需要產(chǎn)生兩個(gè)表中字段的任意組合，這兩個(gè)字段可能是沒有實(shí)際聯(lián)系的?？梢詤⒖枷旅骀溄又嘘P(guān)于每個(gè)班級血型的例子，核心思想也是這個(gè)。
https://blog.csdn.net/xiaolinyouni/article/details/6943337
實(shí)際中應(yīng)該有很多類似的場景。
而模擬循環(huán)是笛卡爾積結(jié)合了字符串截取函數(shù)實(shí)現(xiàn)的，本質(zhì)上還是“組合”。下次再遇到類似場景的時(shí)候，可以考慮下笛卡爾積能否實(shí)現(xiàn)。
以上就是笛卡爾積（SQL查詢中笛卡爾積的巧妙使用）這篇文章的一些介紹，網(wǎng)友如果對笛卡爾積（SQL查詢中笛卡爾積的巧妙使用）有不同看法，希望來共同探討進(jìn)步。
