笛卡尔积|SQL查询中笛卡尔积的巧妙使用

笛卡尔积（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)selec百思特网t * 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 '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