公用表表达式
公共表表达式(CTE)，也称为查询，在用于更大的查询上下文之前为可能复杂的子查询用WITH提供简写名称。这提高SQL代码的可读性。
您可以将CTE与SELECT子句和INSERT、DELETE、UPDATE和UPSERT数据修改语句结合使用。
语法图
- WithCommonTable
- Preparable_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;这种做法适用于测试环境中，但不建议在生产中使用。