SQL查詢中笛卡爾積的巧妙使用

本文透過兩個小例子學習一下笛卡爾積的巧妙使用。後臺回覆“

笛卡爾積

”可以獲取本文pdf版本，便於閱讀儲存。

笛卡爾積，又叫cross join，是SQL中兩表連線的一種方式。

假如A表中的資料為m行，B表中的資料有n行，那麼A和B做笛卡爾積，結果為m*n行。

笛卡爾積的寫法為：

select *from A，B或者select * from A cross join B

通常我們都要在實際SQL中避免直接使用笛卡爾積，因為它會使“資料爆炸”，尤其是資料量很大的時候。但某些時候，巧妙的使用笛卡爾積，反而能快速幫助我們解決實際問題。下面看幾個例子。

with as的用法

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

with tmp as（ select * from class）select * from tmp

上面的寫法先執行select * from class定義（生成）了一箇中間表tmp，然後使用了tmp這個中間表。通常可以用來將固定的查詢抽取出來，只查一次，多次使用，從而提高效率。也可以和union all結合起來構造資料供測試使用，在本文接下來的部分會看到後面場景的這種用法。關於with as的一些要點和注意事項可以參考下面的連結：

https：//blog。csdn。net/baidu_30527569/article/details/48680745

例子1-產生順序值：查詢當日每小時的收入資料，未產生收入的置為0

假設有一張收入表，每過一個小時，就自動更新上一小時的收入資料。但我們希望對於未更新的時間收入值顯示為0。這樣能更好的體現完整性，也便於進行多天資料的對比。如下圖所示：

對於收入非0的小時，我們可以從收入表中直接查詢出當小時的收入資料。收入表結構如下（假設當前收入資料只更新到16點）：

查詢的SQL為：

select dt， hour， incomefrom t_h_incomewhere day = ‘2020-04-19’

顯然，得到的結果不會包含17點及以後的時間。我們可以採用笛卡爾積構造一個小時序列，如下面程式碼所示：

with t_hour as （select ‘00’ as dhourunion all select ‘01’ as dhourunion all select ‘02’ as dhourunion all select ‘03’ as dhourunion all select ‘04’ as dhourunion all select ‘05’ as dhourunion all select ‘06’ as dhourunion all select ‘07’ as dhourunion all select ‘08’ as dhourunion all select ‘09’ as dhourunion all select ‘10’ as dhourunion all select ‘11’ as dhourunion all select ‘12’ as dhourunion all select ‘13’ as dhourunion all select ‘14’ as dhourunion all select ‘15’ as dhourunion all select ‘16’ as dhourunion all select ‘17’ as dhourunion all select ‘18’ as dhourunion all select ‘19’ as dhourunion all select ‘20’ as dhourunion all select ‘21’ as dhourunion all select ‘22’ as dhourunion all select ‘23’ as dhour），t_day as （select ‘2020-04-19’ as dt）select * from t_day， t_hour

得到的結果如下，生成了這一天每個小時的結構。

將上面的結果與原來的資料左關聯，關聯不上的置為0，即可得到想要的結果。程式碼如下：

透過手動構造dt和dhour，用笛卡爾積產生了一個“序列”。而對於dhour的構造，也可以採用笛卡爾積的方式，但需要注意限制範圍不大於23，程式碼如下：

with t_hour as （select ‘0’ as idunion all select ‘1’ as idunion all select ‘2’ as id ），f_hour as （select ‘0’ as idunion all select ‘1’ as idunion all select ‘2’ as idunion all select ‘3’ as idunion all select ‘4’ as idunion all select ‘5’ as idunion all select ‘6’ as idunion all select ‘7’ as idunion all select ‘8’ as idunion all select ‘9’ as id）select concat（a。id， b。id） hourfrom t_hour a， f_hour bwhere concat（a。id， b。id） <= ‘23’order by hour

以上我們都主要使用了笛卡爾積產生順序值的場景，類似的可以構造從00~99的數字，構造之後也可以根據實際需要加入新的限制條件。

例子2-模擬迴圈：遍歷字串，每行按順序輸出一個字元

注：例子來源於《SQL Cookbook》第6章，經過自己的修改。

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

這裡需要笛卡爾積配合字串擷取函式來實現。要實現逐一訪問字串，需要有一箇中間表，儲存序列值，類似於前面提到的序列。我們看下下面的程式碼：

with t5 as （select 1 as posunion all select 2 as pos union allselect 3 as pos union allselect 4 as pos union allselect 5 as pos ），emp as （select ‘KING’ as name）select *from emp， t5

得到的結果如下圖所示：

考慮到字串擷取函式能夠按位置擷取。正好可以用上生成的pos。程式碼如下：

with t5 as （select 1 as posunion all select 2 as pos union allselect 3 as pos union allselect 4 as pos union allselect 5 as pos ），emp as （select ‘KING’ as name）select substr（name， pos， 1）from emp， t5where t5。pos <= length（emp。name）

可以看到使用了pos，就能夠“迴圈”地擷取字串了。需要注意where里加上了迴圈跳出的條件，這也比較好理解：不能擷取超過字串長度的字元。

還可以按照需要調整遍歷時輸出的格式，如下面程式碼和結果所示：

select substr（name， pos） char_name1，substr（name， length（name）-pos+1） char_name1from emp， t5where t5。pos <= length（emp。name）

這個例子中我們利用笛卡爾積模擬迴圈，對字串進行了遍歷。

小結

本文首先學習了with as的用法，然後透過例子總結了兩個巧妙使用笛卡爾積的場景：生成序列和模擬迴圈。雖然在實際中可能用的不是很多，但也體現出了SQL的靈活性。生成序列可以更廣義的理解為：需要產生兩個表中欄位的任意組合，這兩個欄位可能是沒有實際聯絡的。可以參考下面連結中關於每個班級血型的例子，核心思想也是這個。

https：//blog。csdn。net/xiaolinyouni/article/details/6943337

實際中應該有很多類似的場景。

而模擬迴圈是笛卡爾積結合了字串擷取函式實現的，本質上還是“組合”。下次再遇到類似場景的時候，可以考慮下笛卡爾積能否實現。

除此以外《SQL Cookbook》中也提到了笛卡爾積可以用於結果轉置~有機會我們以後再來學習。本文程式碼不是很複雜，後臺回覆“

笛卡爾積

”可以獲取本文pdf版本，便於閱讀儲存。