江苏龙网

  1. 主页 > 生活百科 > >

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


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

文章插图
笛卡尔积(在SQL查询中巧妙使用笛卡尔积)
本文用两个小例子来学习笛卡尔积的巧妙运用 。后台回复“笛卡尔产品”即可获得本文pdf版本 ， 方便阅读和保存 。
笛卡尔积 ， 也称为交叉连接 ， 是SQL中连接两个表的一种方式 。
如果表A的数据是M行 ， 表B的数据是N行 ， 那么A和B做笛卡尔积 ， 结果是m*n行 。
笛卡尔乘积是这样写的:
select *from A,B或者select * from A cross join B通常情况下 ， 我们应该避免在实际的SQL中直接使用笛卡尔积 ， 因为这样会造成“数据爆炸” ， 尤其是当数据量很大的时候 。但有时候 ， 巧妙利用笛卡尔积可以帮助我们快速解决实际问题 。看看下面的例子 。
与as一起使用在此之前 ， 我们先来看看with as的用法 。
with tmp as(select * from class)select * from tmp上述方法通过执行select * from class定义(生成)一个中间表tmp ， 然后使用中间表tmp 。通常可以用来提取固定的查询 ， 只检查一次 ， 多次使用 ， 从而提高效率 。它还可以与union all结合来构造测试数据 ， 我们将在本文下一部分的后面的场景中看到这种用法 。关于as的一些要点和注意事项 ， 请参考以下链接:
https://blog . csdn . net/Baidu _ 30527569/article/details/48680745
例1-生成序列值:查询当天的小时收入数据 ， 如果没有产生收入 ， 则设置为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 *&nbs信息资源网p;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
【SQL查询中笛卡尔积的巧妙使用 笛卡尔积】通过手动构造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)


推荐阅读


上一篇：专科也有“提前批”？ 提前批专科

下一篇：精辟的辞职理由 简短实用的辞职原因有哪些