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

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

笛卡爾積”可以獲取本文pdf版本，便於閱讀保存。

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

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

笛卡爾積的寫法為：

<code>
select
 *
from
 A,B

或者

select
 * 
from
 A 
cross
 
join
 B/<code>

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

with as的用法

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

<code>
with
 tmp  
as
(
    
select
 * 
from
 
class
)

select
 * 
from
 tmp/<code>

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

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

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

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

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

查詢的SQL為：

<code>
select
 dt, 
hour
, income
from
 t_h_income
where
 
day
 = 
'2020-04-19'
/<code>

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

<code>
with
 t_hour 
as
 (
select
 
'00'
 
as
 dhour
union
 
all
 
select
 
'01'
 
as
 dhour
union
 
all
 
 
select
 
'02'
 
as
 dhour
union
 
all
 
select
 
'03'
 
as
 dhour
union
 
all
 
select
 
'04'
 
as
 dhour
union
 
all
 
select
 
'05'
 
as
 dhour
union
 
all
 
select
 
'06'
 
as
 dhour
union
 
all
 
 
select
 
'07'
 
as
 dhour
union
 
all
 
select
 
'08'
 
as
 dhour
union
 
all
 
select
 
'09'
 
as
 dhour
union
 
all
 
select
 
'10'
 
as
 dhour
union
 
all
 
select
 
'11'
 
as
 dhour
union
 
all
 
 
select
 
'12'
 
as
 dhour
union
 
all
 
select
 
'13'
 
as
 dhour
union
 
all
 
select
 
'14'
 
as
 dhour
union
 
all
 
select
 
'15'
 
as
 dhour
union
 
all
 
select
 
'16'
 
as
 dhour
union
 
all
 
 
select
 
'17'
 
as
 dhour
union
 
all
 
select
 
'18'
 
as
 dhour
union
 
all
 
select
 
'19'
 
as
 dhour
union
 
all
 
select
 
'20'
 
as
 dhour
union
 
all
 
select
 
'21'
 
as
 dhour
union
 
all
 
 
select
 
'22'
 
as
 dhour
union
 
all
 
select
 
'23'
 
as
 dhour
),t_day 
as
 
(
select
 
'2020-04-19'
 
as
 dt
)
select
 * 
from
 t_day, t_hour/<code>

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

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

<code>
with
 t_hour 
as
 (
select
 
'00'
 
as
 dhour
union
 
all
 
select
 
'01'
 
as
 dhour
union
 
all
 
select
 
'02'
 
as
 dhour
union
 
all
 
select
 
'03'
 
as
 dhour
union
  
all
 
select
 
'04'
 
as
 dhour
union
 
all
 
select
 
'05'
 
as
 dhour
union
 
all
 
select
 
'06'
 
as
 dhour
union
 
all
 
select
 
'07'
 
as
 dhour
union
 
all
 
select
 
'08'
 
as
 dhour
union
  
all
 
select
 
'09'
 
as
 dhour
union
 
all
 
select
 
'10'
 
as
 dhour
union
 
all
 
select
 
'11'
 
as
 dhour
union
 
all
 
select
 
'12'
 
as
 dhour
union
 
all
 
select
 
'13'
 
as
 dhour
union
  
all
 
select
 
'14'
 
as
 dhour
union
 
all
 
select
 
'15'
 
as
 dhour
union
 
all
 
select
 
'16'
 
as
 dhour
union
 
all
 
select
 
'17'
 
as
 dhour
union
 
all
 
select
 
'18'
 
as
 dhour
union
  
all
 
select
 
'19'
 
as
 dhour
union
 
all
 
select
 
'20'
 
as
 dhour
union
 
all
 
select
 
'21'
 
as
 dhour
union
 
all
 
select
 
'22'
 
as
 dhour
union
 
all
 
select
 
'23'
 
as
 dhour
),t_day 
as
 
(
 
select
 
'2020-04-19'
 
as
 dt
)

select
 * 
from
 t_day, t_hour

select
 a.dt, a.dhour, 
case
 
when
 b.income 
is
 
null
 
then
 
0
 
else
 b.income 
end
 
as
 income
from
(
select
 dt, dhour
from
 t_day, t_hour
) a 
left
 
join
 
t_h_income b
on
 a.dt = b.dt 
and
 a.dhour = b.hour/<code>

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

<code>
with
 t_hour 
as
 
(
select
 
'0'
 
as
 
id
union
 
all
 
select
 
'1'
 
as
 
id
union
 
all
 
select
 
'2'
 
as
 
id
 
),
f_hour 
as
 (
select 
 
'0'
 
as
 
id
union
 
all
 
select
 
'1'
 
as
 
id
union
 
all
 
select
 
'2'
 
as
 
id
union
 
all
 
select
 
'3'
 
as
 
id
union
 
all
 
select
 
'4' 
 
as
 
id
union
 
all
 
select
 
'5'
 
as
 
id
union
 
all
 
select
 
'6'
 
as
 
id
union
 
all
 
select
 
'7'
 
as
 
id
union
 
all
 
select
 
'8'
 
as 
 
id
union
 
all
 
select
 
'9'
 
as
 
id
)

select
 
concat
(a.id, b.id) 
hour
from
 t_hour a, f_hour b
where
 
concat
(a.id, b.id) <= 
'23'
order
 
by
 
hour
/<code>

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

例子2-模擬循環：遍歷字符串，每行按順序輸出一個字符

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

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

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

<code>
with
 t5 
as
 
(
select
 
1
 
as
 pos
union
 
all
 
select
 
2
 
as
 pos 
union
 
all
select
 
3
 
as
 pos 
union
 
all
 
select
 
4
 
as
 pos 
union
 
all
select
 
5
 
as
 pos 
),
emp 
as
 
(
select
 
'KING'
 
as
 
name
)

select
 *
from
 emp, t5/<code>

得到的結果如下圖所示：

考慮到字符串截取函數能夠按位置截取。正好可以用上生成的pos。代碼如下:

<code>
with
 t5 
as
 
(
select
 
1
 
as
 pos
union
 
all
 
select
 
2
 
as
 pos 
union
 
all
select
 
3
 
as
 pos 
union
 
all
select
 
4
 
as
 pos 
union
  
all
select
 
5
 
as
 pos 
),
emp 
as
 
(
select
 
'KING'
 
as
 
name
)

select
 
substr
(
name
, pos, 
1
)
from
 emp, t5
where
 t5.pos <= 
length
(emp.name)/<code>

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

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

<code>
select
 
substr
(
name
, pos) char_name1,
substr
(
name
, 
length
(
name
)-pos+
1
) char_name1
from
 emp, t5
where
 t5.pos <= 
length
(emp.name)/<code>

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

小結

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

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

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

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

除此以外《SQL Cookbook》中也提到了笛卡爾積可以用於結果轉置~有機會我們以後再來學習。本文代碼不是很複雜，後臺回覆“笛卡爾積”可以獲取本文pdf版本，便於閱讀保存。