《PostgreSQL 开发指南》第 20 篇 通用表表达式

通用表表达式（Common Table Expression）是一个临时的查询结果或者临时表，可以在其他SELECT
、INSERT
、UPDATE
以及DELETE
语句中使用。通用表表达式只在当前语句中有效，类似于子查询。

使用 CTE 的主要好处包括：

• 提高复杂查询的可读性。CTE 可以将复杂查询模块化，组织成容易理解的结构。

• 支持递归查询。CTE 通过引用自身实现递归，可以方便地处理层次结构数据和图结构数据。

简单 CTE

通用表表达式的定义如下：

WITH cte_name (col1, col2, ...) AS (
    cte_query_definition
)
sql_statement;

其中，

• WITH
  表示定义 CTE，因此 CTE 也称为WITH
  查询；

• cte_name 指定了 CTE 的名称，后面是可选的字段名；

• 括号内是 CTE 的内容，可以是SELECT
  语句，也可以是INSERT
  、UPDATE
  、DELETE
  语句；

• sql_statement 是主查询语句，可以引用前面定义的 CTE。该语句同样可以是SELECT
  、INSERT
  、UPDATE
  或者DELETE
  。

PostgreSQL 中的 CTE 通常用于简化复杂的连接查询或子查询。例如：

WITH department_avg(department_id, avg_salary) AS (
  SELECT department_id,
         AVG(salary) AS avg_salary
    FROM employees
   GROUP BY department_id
)
SELECT d.department_name,
       da.avg_salary
  FROM departments d
  JOIN department_avg da
    ON (d.department_id = da.department_id)
 ORDER BY d.department_name;
department_name |avg_salary            |
----------------|----------------------|
Accounting      |10154.0000000000000000|
Administration  | 4400.0000000000000000|
Executive       |    19333.333333333333|
...

首先，我们定义了一个名为 department_avg 的 CTE，表示每个部门的平均月薪；然后和 departments 表进行连接查询。虽然用其他方式也可以实现相同的功能，但是 CTE 让代码显得更加清晰易懂。

一个WITH
关键字可以定义多个 CTE，而且后面的 CTE 可以引用前面的 CTE。例如：

WITH cte1(n) AS (
  SELECT 1
),
cte2(m) as (
  select n+1 from cte1
)
SELECT *
  FROM cte1, cte2;
n|m|
-|-|
1|2|

以上示例中定义了两个 CTE，其中 cte2 引用了 cte1。最后的查询使用两者进行连接查询。

递归 CTE

递归 CTE 允许在它的定义中进行自引用，理论上来说可以实现任何复杂的计算功能，最常用的场景就是遍历层次结构的数据和图结构数据。

WITH RECURSIVE cte_name AS(
    cte_query_initial -- 初始化部分
    UNION [ALL]
    cte_query_iterative  -- 递归部分
) SELECT * FROM cte_name;

其中，

• RECURSIVE
  表示递归 CTE；

• cte_query_initial 是初始化查询，用于创建初始结果集；

• cte_query_iterative 是递归部分，可以引用 cte_name；

• 如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归并返回结果。

一个经典的递归 CTE 案例就是生成数字序列：

WITH RECURSIVE t(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n+1 FROM t WHERE n < 10
)
SELECT n FROM t;
n |
--|
 1|
 2|
 3|
 4|
 5|
 6|
 7|
 8|
 9|
10|

以上语句执行过程如下：

• 执行 CTE 中的初始化查询，生成一行数据（1）；

• 第一次执行递归查询，判断 n < 10，生成一行数据 2（n+1）；

• 重复执行递归查询，生成更多的数据；直到 n = 10 终止；此时临时表 t 中包含 10 条数据；

• 执行主查询，返回所有的数据。

注意，如果没有指定终止条件，上面的查询将会进入死循环。

接下来我们看一个更实用的案例，通过递归 CTE 遍历组织结构。

WITH RECURSIVE employee_path (employee_id, employee_name, path) AS
(
  SELECT employee_id, CONCAT(first_name, ',', last_name), CONCAT(first_name, ',', last_name) AS path
    FROM employees
   WHERE manager_id IS NULL
   UNION ALL
  SELECT e.employee_id, CONCAT(e.first_name, ',', e.last_name), CONCAT(ep.path, '->', e.first_name, ',', e.last_name)
    FROM employee_path ep
    JOIN employees e ON ep.employee_id = e.manager_id
)
SELECT * FROM employee_path ORDER BY employee_id;
employee_id|employee_name    |path                                                          |
-----------|-----------------|--------------------------------------------------------------|
        100|Steven,King      |Steven,King                                                   |
        101|Neena,Kochhar    |Steven,King->Neena,Kochhar                                    |
        102|Lex,De Haan      |Steven,King->Lex,De Haan                                      |
        103|Alexander,Hunold |Steven,King->Lex,De Haan->Alexander,                          |
        104|Bruce,Ernst      |Steven,King->Lex,De Haan->Alexander,Hunold->Bruce,Ernst       |
        105|David,Austin     |Steven,King->Lex,De Haan->Alexander,Hunold->David,Austin      |
        106|Valli,Pataballa  |Steven,King->Lex,De Haan->Alexander,Hunold->Valli,Pataballa   |
...

其中，初始化查询语句返回了公司最高层的领导（manager_id IS NULL），也就是“Steven,King”；递归查询将员工表的 manager_id 与已有结果集中的 employee_id 关联，获取每个员工的下一级员工，直到无法找到新的数据；path 字段存储了每个员工从上至下的管理路径。

当然，我们也可以对组织结构从下至上进行遍历。更多关于递归 CTE 的实际应用场景，可以参考这篇文章。

DML 语句与 CTE

除了SELECT
语句之外，INSERT
、UPDATE
或者DELETE
语句也可以与 CTE 一起使用。我们可以在 CTE 中使用 DML 语句，也可以将 CTE 用于 DML 语句。

如果在 CTE 中使用 DML 语句，我们可以将数据修改操作影响的结果作为一个临时表，然后在其他语句中使用。例如：

-- 创建一个员工历史表
CREATE TABLE employees_history
AS SELECT * FROM employees WHERE 1 = 0;

with deletes as (
  delete from employees
   where employee_id = 206
   returning *
)
insert into employees_history
select * from deletes;

select employee_id, first_name, last_name 
from employees_history;
employee_id|first_name|last_name|
-----------|----------|---------|
        206|William   |Gietz    |

我们首先创建了一个记录员工历史信息的 employees_history 表；然后使用DELETE
语句定义了一个 CTE，returning *
返回了被删除的数据，构成了结果集 deletes；然后使用INSERT
语句记录被删除的员工信息。

接下来我们将该员工添加回员工表：

with inserts as (
  insert into employees
  values (206,'William','Gietz','WGIETZ','515.123.8181','2002-06-07','AC_ACCOUNT',8800.00,NULL,205,110)
  returning *
)
insert into employees_history
select * from inserts;

除了插入数据到 employees 表之外，我们还利用 CTE 在表 employees_history 中增加了一条历史记录，现在该表中有两条数据。

CTE 中的UPDATE
语句有些不同，因为更新的数据分为更新之前的状态和更新之后的状态。例如：

delete from employees_history;-- 清除历史记录

with updates as (
  update employees
     set salary = salary + 500
   where employee_id = 206
   returning *
)
insert into employees_history
select * from employees where employee_id = 206;

select employee_id, salary from employees_history;
employee_id|salary |
-----------|-------|
        206|8300.00|

在 CTE 中，UPDATE
语句修改了一个员工的月薪；但是为了记录修改之前的数据，我们插入 employees_history 的数据仍然来自 employees 表。因为在一个语句中，所有的操作都在一个事务中，所以主查询中的 employees 是修改之前的状态。

如果想要获取更新之后的数据，直接使用 updates 即可：

with updates as (
  update employees
     set salary = salary - 500
   where employee_id = 206
   returning *
)
select employee_id,first_name, last_name, salary
from updates;
employee_id|first_name|last_name|salary |
-----------|----------|---------|-------|
        206|William   |Gietz    |8300.00|