公用表表达式

公共表表达式(CTE),也称为查询,在用于更大的查询上下文之前为可能复杂的子查询用WITH提供简写名称。这提高SQL代码的可读性。

您可以将CTE与SELECT子句和INSERTDELETEUPDATEUPSERT数据修改语句结合使用。

语法图

WithCommonTable
WITHRECURSIVEtable_namenameASNOTMATERIALIZEDPrepar_Stmt
Preparable_Stmt
insert_stmtupdate_stmtdelete_stmtupsert_stmtselect_stmt

参数介绍

参数说明
table_name用于引用随附查询或语句中的公用表表达式的名称。
name新定义的公用表表达式中的列之一的名称。
prepar_stmt用作公用表表达式的语句或子查询。
table_name需要添加列的表

说明

语句为子查询的结果WITH x AS (y)创建临时表名,以便在上下文中重用。

示例如下:

with e as (select * from  emp where deptno=30)
  select e.empno,e.ename,d.loc from e left join dept as d  on e.deptno = d.deptno ;
   empno | ename  |   loc
--------+--------+----------
   7698 | BLAKE  | CHICAGO
   7499 | ALLEN  | CHICAGO
   7844 | TURNER | CHICAGO
   7521 | WARD   | CHICAGO
   7654 | MARTIN | CHICAGO
   7900 | JAMES  | CHICAGO

在以上示例中,子句定义了子查询,用WITH引用了临时名称e,并且该名称成为表名,用于后续子句的表达式中。

也可以使用单个WITH子句同时定义多个公用表表达式,用逗号分隔。后面的子查询可以按名称引用前面的子查询。例如:

with e as (select * from  emp where deptno=30),
res as (select e.empno,e.ename,d.loc from e left join dept as d  on e.deptno = d.deptno)
select * from res;
   empno | ename  |   loc
--------+--------+----------
   7698 | BLAKE  | CHICAGO
   7499 | ALLEN  | CHICAGO
   7844 | TURNER | CHICAGO
   7521 | WARD   | CHICAGO
   7654 | MARTIN | CHICAGO
   7900 | JAMES  | CHICAGO

在此示例中,第二个CTE按名称res引用了e

用WITH嵌套子语句

可以WITH在子查询中使用子句,WITH在另一个子句中使用WITH子句。例如:

with  d as
    (select  *  from 
         (with dn as (select  *  from dept ) select  *  from dn))
select  *  from  d;
 deptno |   dname    |   loc
---------+------------+-----------
      50 | BOSS       | BEIJING
      10 | ACCOUNTING | NEW YORK
      20 | RESEARCH   | DALLAS
      30 | SALES      | CHICAGO
      40 | OPERATIONS | BOSTON
      60 | programmer | bj

数据变更语句

包含数据变更语句的子句位于查询的上层,您就可以将数据修改语句用作公共表表达式。例如:

with loc as
   (insert into dept (deptno,dname,loc)
values (90,'CTO','SHANGHAI') 
returning dname)
select dname from loc;
  dname
---------
  CTO 

递归公用表表达式

递归公用表表达式是包含引用其自身输出的子查询的公用表表达式。

递归 CTE定义采用以下形式:

WITH RECURSIVE <cte name> (<columns>) AS (
    <initial subquery>
  [UNION | UNION ALL]
    <recursive subquery>
)
<query>

编写递归 CTE:

  • 在 CTE定义中的运算符之后和 CTE名称之前直接添加RECURSIVE关键字。
  • 定义一个初始的非递归子查询,此子查询定义 CTE的初始值。
  • 在初始子查询之后添加UNION、UNION ALL关键字。该UNION变体对行进行重复数据删除。
  • 定义一个引用它自己的输出的递归子查询。与初始子查询不同,此子查询还可以引用CTE名称。
  • 编写一个评估 CTE结果的父查询。

Hubble评估递归 CTE如下:

  • 评估初始查询。其结果存储在CTE中的行中,并复制到临时工作表中。此工作表在递归子查询的迭代中更新。
  • 在工作表的内容上迭代地评估递归子查询。每次迭代的结果都会替换工作表的内容。结果也存储到CTE的行中。递归子查询迭代直到没有结果返回。

举例如下:

以下递归CTE计算数字 0到10的阶乘:

with RECURSIVE w (n, f) as (
    values (0, 1) 
  union all
    select n+1, (n+1)*f from w where n < 10 
)
select * from  w;
n  |    f
-----+----------
   0 |       1
   1 |       1
   2 |       2
   3 |       6
   4 |      24
   5 |     120
   6 |     720
   7 |    5040
   8 |   40320
   9 |  362880
  10 | 3628800

如果WHERE示例中未定义子句,则递归子查询将始终返回结果并无限循环,从而导致错误:

with RECURSIVE w (n, f) as (
    values (0, 1) 
  union all
    select n+1, (n+1)*f from w 
)
select * from  w;
ERROR: integer out of range
SQLSTATE: 22003

如果您不确定您的递归子查询是否会无限循环,您可以使用LIMIT关键字限制CTE的结果,避免integer out of range错误:

with RECURSIVE w (n, f) as (
    values (0, 1) 
  union all
    select n+1, (n+1)*f from w 
)
select * from  w limit 11;

这种做法适用于测试环境中,但不建议在生产中使用。