Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

common table expression

··232 字
sql 概念 有悟方法

使用 with 表达式来美化、步骤化篇幅很长的SQL。

假如你的SQL有很多子查询 #

在实际数据开发项目中,有些SQL非常复杂,一个SQL嵌套5、6个子查询也很正常。为了可读性,应该减少嵌套层级。这时可以使用 with 表达来达到目的,其学名叫 common table expression,简称 cte。它有好处是将子查询步骤化,还可以复用。

在java、javascript、python、c等编程语言中,如何你看到有人使用 5 层 if-else 嵌套的程序时,几乎几一例外地都会吐槽一番。在编程的最佳实践中,也会建议你减少嵌套,除代码可读性差之外,还增加了逻辑的复杂度。

使用 sql 编程时,也是如此。过多的子查询嵌套,会极大的影响可读性,并且逻辑关系表达极不清晰。有悟敢肯定的,没有人写SQL时,会跟有悟一样有格式偏执。

  • select 所选择的列,一定是按字段分行,逗号在前面
  • 左对齐(绝不右对齐,每列开头的逗号除外)
  • 把SQL看成空格分割的两部分,左边是关键字和逗号,右边部分是字段名、表名、逻辑判断条件等
  • 子查询按级别缩进

以上是为了保持整齐,视觉上的整齐。再配合带有语法高亮的现代编辑器,肉眼可以非常习惯的找到对应的内容,并可以减少非常多的低级拼写错误,提高效率。

select site_name
     , category
     , description
     , pages_count
from   websites
left   join ...
on     ...
where  site_name = 'youwu.today'
and    ...

基于此,可以想象的,当嵌套达到3级以上,子查询经常因为逻辑判断条件、关联条件语句较长很容易往右伸展而超出一个屏幕大小,它不仅会影响阅读,更重要的是有时会因为代码拆行,低级错误没有被发现。这个细节可能很多人没有注意到。

根据有悟多年来改进并养成的习惯(多花几秒钟,也要把SQL排整齐,自己看着舒服,别人看着也不会讨厌),大概长这样。

with dat1 as (
    select site_name
         , category
         , description
         , pages_count
    from   websites
    left   join ...
    on     ...
    where  site_name = 'youwu.today'
    and    ...
)
, dat2 as (
    select ...
    from   table_name2 tab2
    inner  join dat1
    on     dat1.site_name = tab2.site_name
    and    ...
    group  by ...
)
, dat3 as (
	select ...
	from   table_name3, dat2
	where  ....
)
select ...
from   dat3
...

刚开始使用时,可能会感到别扭。其实可把每一个 common table expression 子查询视为一个不是手工物理化的临时表(数据库会根据需要,自行决定是否真正创建临时中间结果来保存子查询。如在oracle上执行,可以通过 execute plan 执行计划来观察)。而当这个结果在同一个查询中被多次引用时,可以直接使用子查询的别名,这是普通子查询语句所无法做到的(有时你只有查询但没有建表的权限)。

额外收获(bonus) #

使用 CTE ,还有一个额外的功能。

有悟说过,SQL是一个过程。编写SQL时,若需要使用多个子查询时来完成一个复杂的查询SQL语句时,比较难确定其正确性。

使用 CTE 的形式来编写这个查询,如上节所列SQL示例,

with dat1 as (
    select site_name
         , category
         , description
         , pages_count
    from   websites
    left   join ...
    on     ...
    where  site_name = 'youwu.today'
    and    ...
)
-- select *
-- from   dat1;
, dat2 as (
    select ...
    from   table_name2 tab2
    inner  join dat1
    on     dat1.site_name = tab2.site_name
    and    ...
    group  by ...
)
select *
from   dat2;
, dat3 as (
	select ...
	from   table_name3, dat2
	where  ....
)
select ...
from   dat3
...   

在一个 CTE 后,插入 select * from CTE别名 ; 测试语句。这时从 开头with; 之间,就形成了一个具有符合语法又可以执行的SQL,可以方便的观察这个子查询执行结果是否正确。当需要观察后面其它的 CTE,注释掉前面的测试语句,在这个需要测试的子查询后面再插入select * from CTE别名 ;。若当数据量比较大,或者发现结果中出现期望外的数据时,还可以指定的ID号或者条件插入到子查询中进行测试。当每个需要观察的CTE 都经过确认后,把所有测试语句删掉,SQL语句又完好如初。

是不是很方便(在数据开发领域,一个查询带有好几个子查询非常常见)😎!

  1. 将SQL步骤化
  2. 利用现有SQL测试,快速查找问题(数据质量往往导致出现一些意想不到的异常)
  3. 像极编程中的断点调试
  4. 无须破坏代码,无须重新编写测试代码
  5. 子查询复用