大家好，今日我們來聊聊一篇關(guān)于笛卡爾積，SQL查詢中笛卡爾積的巧妙使用的文章，希望對大家有所幫助

笛卡爾積(在SQL查詢中巧妙使用笛卡爾積)

本文用兩個小例子來學習笛卡爾積的巧妙運用。后臺回復“笛卡爾產(chǎn)品”即可獲得本文pdf版本，方便閱讀和保存。

笛卡爾積，也稱為交叉連接，是SQL中連接兩個表的一種方式。

如果表A的數(shù)據(jù)是M行，表B的數(shù)據(jù)是N行，那么A和B做笛卡爾積，結(jié)果是m*n行。

笛卡爾乘積是這樣寫的：

Select*fromA，B或select*fromAcrossjoinB通常情況下，我們應(yīng)該避免在實際的SQL中直接使用笛卡爾積，因為這樣會造成“數(shù)據(jù)爆炸”，尤其是在數(shù)據(jù)量很大的時候。但有時候，巧妙利用笛卡爾積可以幫助我們快速解決實際問題?？纯聪旅娴睦?。

在此之前，我們先來看看with as的用法。

使用tmpa(select * from class)select * from tmp。上面的編寫方法首先通過執(zhí)行select * from class定義(生成)一個中間表tmp，然后使用中間表tmp。通常可以用來提取固定的查詢，只檢查一次，多次使用，從而提高效率。它還可以與union all結(jié)合來構(gòu)造測試數(shù)據(jù)，我們將在本文下一部分的后面的場景中看到這種用法。關(guān)于as的一些要點和注意事項，請參考以下鏈接：

https://blog.csdn.net/baidu_30527569/article/details/48680745

假設(shè)有一張損益表，每過一個小時，就會自動更新前一個小時的收入數(shù)據(jù)。但是，對于沒有更新的時間，我們希望收入值為0。這樣可以更好的體現(xiàn)完整性，也便于多日數(shù)據(jù)的對比。如下圖所示：

對于收益非零的小時，我們可以直接從收益表中查詢當前小時的收益數(shù)據(jù)。收益表結(jié)構(gòu)如下(假設(shè)當前收益數(shù)據(jù)只更新到16點):

查詢SQL是：

DT，HOUR，income fromt _ H _ income whereday=' 2020-04-19 '顯然，得到的結(jié)果不會包括17點以后的時間。我們可以使用笛卡爾積來構(gòu)造一個小時序列，如下面的代碼所示：

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

通過手動構(gòu)造dt和dhour，用笛卡爾積產(chǎn)生了一個“序列”。而對于dhour的構(gòu)造，也可以采用笛卡爾積的方式，但需要注意限制范圍不大于23，代碼如下：

witht_houras(select&#3深圳生活網(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ù)實際需要加入新的限制條件。

注：例子來源于《SQL Cookbook》第6章，經(jīng)過自己的修改。

問題：考慮用SQL實現(xiàn)：將表emp中name為KING的字符串顯示為4行，每行包含其中一個字符。

這里需要笛卡爾積配合字符串截取函數(shù)來實現(xiàn)。要實現(xiàn)逐一訪問字符串，需要有一個中間表，存儲序列值，類似于前面提到的序列。我們看下下面的代碼：

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)整遍歷時輸出的格式，如下面代碼和結(jié)果所示：

selectsubstr(name,pos)char_name1,substr(name,length(name)-pos+1)char_name1fromemp,t5wheret5.pos<=length(emp.name)

這個例子中我們利用笛卡爾積模擬循環(huán)，對字符串進行了遍歷。

本文首先學習了with as的用法，然后通過例子總結(jié)了兩個巧妙使用笛卡爾積的場景：生成序列和模擬循環(huán)。雖然在實際中可能用的不是很多，但也體現(xiàn)出了SQL的靈活性。生成序列可以更廣義的理解為：需要產(chǎn)生兩個表中字段的任意組合，這兩個字段可能是沒有實際聯(lián)系的。可以參考下面鏈接中關(guān)于每個班級血型的例子，核心思想也是這個。

https://blog.csdn.net/xiaolinyouni/article/details/6943337

實際中應(yīng)該有很多類似的場景。

而模擬循環(huán)是笛卡爾積結(jié)合了字符串截取函數(shù)實現(xiàn)的，本質(zhì)上還是“組合”。下次再遇到類似場景的時候，可以考慮下笛卡爾積能否實現(xiàn)。

以上就是笛卡爾積（SQL查詢中笛卡爾積的巧妙使用）這篇文章的一些介紹，網(wǎng)友如果對笛卡爾積（SQL查詢中笛卡爾積的巧妙使用）有不同看法，希望來共同探討進步。