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

大家好，今日我们来聊聊一篇关于笛卡尔积，SQL查询中笛卡尔积的巧妙使用的文章，希望对大家有所帮助

笛卡尔积(在SQL查询中巧妙使用笛卡尔积)

本文用两个小例子来学习笛卡尔积的巧妙运用。后台回复“笛卡尔产品”即可获得本文pdf版本，方便阅读和保存。

笛卡尔积，也称为交叉连接，是SQL中连接两个表的一种方式。

如果表A的数据是M行，表B的数据是N行，那么A和B做笛卡尔积，结果是m*n行。

笛卡尔乘积是这样写的：

Select*fromA，B或select*fromAcrossjoinB通常情况下，我们应该避免在实际的SQL中直接使用笛卡尔积，因为这样会造成“数据爆炸”，尤其是在数据量很大的时候。但有时候，巧妙利用笛卡尔积可以帮助我们快速解决实际问题。看看下面的例子。

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

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

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

假设有一张损益表，每过一个小时，就会自动更新前一个小时的收入数据。但是，对于没有更新的时间，我们希望收入值为0。这样可以更好的体现完整性，也便于多日数据的对比。如下图所示：

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

查询SQL是：

DT，HOUR，income fromt _ H _ income whereday=' 2020-04-19 '显然，得到的结果不会包括17点以后的时间。我们可以使用笛卡尔积来构造一个小时序列，如下面的代码所示：

with t _ houras(select ' 00 ' asdhourunionallselect ' 01 ' asdhourunionallselect ' 02 ' asdhourunionallselect ' 03 ' asdhourunionallselect ' 04 ' asdhourunionallselect ' 05 ' ASD hourinoniallselect ' 07 ' asdhourunionallselect ' 08 ' asdhourunionallselect ' 09 ' asdhourunionallselect ' 10 ' asdhourunionallselect ' 11 ' asdh ourunionallall

将上面的结果与左边的原始数据相关联，将未关联的结果设置为0，以获得所需的结果。代码如下：

with t _ houras(select ' 00 ' asdhourunionallselect ' 01 ' asdhourunionallselect ' 02 ' asdhourunionallselect ' 03 ' asdhourunionallselect ' 04 ' asdhourunionallselect ' 05 ' ASD houriunionallselect ' 06 ' asdhourunionallselect ' 07 ' asdhouru

通过手动构造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查询中笛卡尔积的巧妙使用）这篇文章的一些介绍，网友如果对笛卡尔积（SQL查询中笛卡尔积的巧妙使用）有不同看法，希望来共同探讨进步。