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

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

本文用兩個(gè)小例子來(lái)學(xué)習(xí)笛卡爾積的巧妙運(yùn)用。后臺(tái)回復(fù)“笛卡爾產(chǎn)品”即可獲得本文pdf版本，方便閱讀和保存。

笛卡爾積，也稱(chēng)為交叉連接，是SQL中連接兩個(gè)表的一種方式。

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

笛卡爾乘積是這樣寫(xiě)的：

Select*fromA，B或select*fromAcrossjoinB通常情況下，我們應(yīng)該避免在實(shí)際的SQL中直接使用笛卡爾積，因?yàn)檫@樣會(huì)造成“數(shù)據(jù)爆炸”，尤其是在數(shù)據(jù)量很大的時(shí)候。但有時(shí)候，巧妙利用笛卡爾積可以幫助我們快速解決實(shí)際問(wèn)題。看看下面的例子。

在此之前，我們先來(lái)看看with as的用法。

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

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

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

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

查詢(xún)SQL是：

DT，HOUR，income fromt _ H _ income whereday=' 2020-04-19 '顯然，得到的結(jié)果不會(huì)包括17點(diǎn)以后的時(shí)間。我們可以使用笛卡爾積來(lái)構(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

通過(guò)手動(dòng)構(gòu)造dt和dhour，用笛卡爾積產(chǎn)生了一個(gè)“序列”。而對(duì)于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)生順序值的場(chǎng)景，類(lèi)似的可以構(gòu)造從00~99的數(shù)字，構(gòu)造之后也可以根據(jù)實(shí)際需要加入新的限制條件。

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

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

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

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)跳出的條件，這也比較好理解：不能截取超過(guò)字符串長(zhǎng)度的字符。

還可以按照需要調(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)，對(duì)字符串進(jìn)行了遍歷。

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

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

實(shí)際中應(yīng)該有很多類(lèi)似的場(chǎng)景。

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

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