笛卡尔积（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，即可得到想要的结果。代码如下：

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 &百思特网#39;23' as dhour
),t_day as 
(select '2020-04-19' as dt
)select * from t_day, t_hourselect a.dt, a.dhour, case when b.income is null then 0 else b.income end as incomefrom(select dt, dhourfrom t_day, t_hour
) a left join 
t百思特网_h_income bon a.dt = b.dt and a.dhour = b.hour

通过手动构造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版本，便于阅读保存。