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

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

本文通过两个小例子学习一下笛卡尔积的巧妙使用。后台回复“笛卡尔积”可以获取本文pdf版本，便于阅读保存。

笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。

假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行。

笛卡尔积的写法为：

select*fromA,B或者select*fromAcrossjoinB

通常我们都要在实际SQL中避免直接使用笛卡尔积，因为它会使“数据爆炸”，尤其是数据量很大的时候。但某些时候，巧妙的使用笛卡尔积，反而能快速帮助我们解决实际问题。下面看几个例子。

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

withtmpas(select*fromclass)select*fromtmp

上面的写法先执行select * from class定义(生成)了一个中间表tmp，然后使用了tmp这个中间表。通常可以用来将固定的查询抽取出来，只查一次，多次使用，从而提高效率。也可以和union all结合起来构造数据供测试使用，在本文接下来的部分会看到后面场景的这种用法。关于with as的一些要点和注意事项可以参考下面的链接：

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

假设有一张收入表，每过一个小时，就自动更新上一小时的收入数据。但我们希望对于未更新的时间收入值显示为0。这样能更好的体现完整性，也便于进行多天数据的对比。如下图所示：

对于收入非0的小时，我们可以从收入表中直接查询出当小时的收入数据。收入表结构如下(假设当前收入数据只更新到16点)：

查询的SQL为：

selectdt,hour,incomefromt_h_incomewhereday='2020-04-19'

显然，得到的结果不会包含17点及以后的时间。我们可以采用笛卡尔积构造一个小时序列，如下面代码所示：

witht_houras(select'00'asdhourunionallselect'01'asdhourunionallselect'02'asdhourunionallselect'03'asdhourunionallselect'04'asdhourunionallselect'05'asdhourunionallselect'06'asdhourunionallselect'07'asdhourunionallselect'08'asdhourunionallselect'09'asdhourunionallselect'10'asdhourunionallselect'11'asdhourunionallselect'12'asdhourunionallselect'13'asdhourunionallselect'14'asdhourunionallselect'15'asdhourunionallselect'16'asdhourunionallselect'17'asdhourunionallselect'18'asdhourunionallselect'19'asdhourunionallselect'20'asdhourunionallselect'21'asdhourunionallselect'22'asdhourunionallselect'23'asdhour),t_dayas(select'2020-04-19'asdt)select*fromt_day,t_hour

得到的结果如下，生成了这一天每个小时的结构。

将上面的结果与原来的数据左关联，关联不上的置为0，即可得到想要的结果。代码如下：

witht_houras(select'00'asdhourunionallselect'01'asdhourunionallselect'02'asdhourunionallselect'03'asdhourunionallselect'04'asdhourunionallselect'05'asdhourunionallselect'06'asdhourunionallselect'07'asdhourunionallselect'08'asdhourunionallselect'09'asdhourunionallselect'10'asdhourunionallselect'11'asdhourunionallselect'12'asdhourunionallselect'13'asdhourunionallselect'14'asdhourunionallselect'15'asdhourunion&n深圳生活网bsp;allselect'16'asdhourunionallselect'17'asdhourunionallselect'18'asdhourunionallselect'19'asdhourunionallselect'20'asdhourunionallselect'21'asdhourunionallselect'22'asdhourunionallselect'23'asdhour),t_dayas深圳生活网(select'2020-04-19'asdt)select*fromt_day,t_hourselecta.dt,a.dhour,casewhenb.incomeisnullthen0elseb.incomeendasincomefrom(selectdt,dhourfromt_day,t_hour)aleftjoint_h_incomebona.dt=b.dtanda.dhour=b.hour

通过手动构造dt和dhour，用笛卡尔积产生了一个“序列”。而对于dhour的构造，也可以采用笛卡尔积的方式，但需要注意限制范围不大于23，代码如下：

witht_houras(select&#3深圳生活网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

以上我们都主要使用了笛卡尔积产生顺序值的场景，类似的可以构造从00~99的数字，构造之后也可以根据实际需要加入新的限制条件。

注：例子来源于《SQL Cookbook》第6章，经过自己的修改。

问题：考虑用SQL实现：将表emp中name为KING的字符串显示为4行，每行包含其中一个字符。

这里需要笛卡尔积配合字符串截取函数来实现。要实现逐一访问字符串，需要有一个中间表，存储序列值，类似于前面提到的序列。我们看下下面的代码：

witht5as(select1asposunionallselect2asposunionallselect3asposunionallselect4asposunionallselect5aspos),empas(select'KING'asname)select*fromemp,t5

得到的结果如下图所示：

考虑到字符串截取函数能够按位置截取。正好可以用上生成的pos。代码如下:

witht5as(select1asposunionallselect2asposunionallselect3asposunionallselect4asposunionallselect5aspos),empas(select'KING'asname)selectsubstr(name,pos,1)fromemp,t5wheret5.pos<=length(emp.name)

可以看到使用了pos，就能够“循环”地截取字符串了。需要注意where里加上了循环跳出的条件，这也比较好理解：不能截取超过字符串长度的字符。

还可以按照需要调整遍历时输出的格式，如下面代码和结果所示：

selectsubstr(name,pos)char_name1,substr(name,length(name)-pos+1)char_name1fromemp,t5wheret5.pos<=length(emp.name)

这个例子中我们利用笛卡尔积模拟循环，对字符串进行了遍历。

本文首先学习了with as的用法，然后通过例子总结了两个巧妙使用笛卡尔积的场景：生成序列和模拟循环。虽然在实际中可能用的不是很多，但也体现出了SQL的灵活性。生成序列可以更广义的理解为：需要产生两个表中字段的任意组合，这两个字段可能是没有实际联系的。可以参考下面链接中关于每个班级血型的例子，核心思想也是这个。

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

实际中应该有很多类似的场景。

而模拟循环是笛卡尔积结合了字符串截取函数实现的，本质上还是“组合”。下次再遇到类似场景的时候，可以考虑下笛卡尔积能否实现。

除此以外《SQL Cookbook》中也提到了笛卡尔积可以用于结果转置~有机会我们以后再来学习。本文代码不是很复杂，后台回复“笛卡尔积”可以获取本文pdf版本，便于阅读保存。

　　免责声明：本文由用户上传，与本网站立场无关。财经信息仅供读者参考，并不构成投资建议。投资者据此操作，风险自担。 如有侵权请联系删除！