common table expression

使用 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语句又完好如初。

是不是很方便（在数据开发领域，一个查询带有好几个子查询非常常见）😎！

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