# 公用表表达式（WITH）

> 使用 WITH 组织查询，以原子方式移动行，并递归遍历树结构。

---

LLMS 索引： [llms.txt](/llms.txt)

---

*公用表表达式*（CTE）是一个由 `WITH name AS (SELECT ...)` 引入的命名子查询。你可以从主查询（以及后续的 CTE）中引用它，就像引用一张表一样。CTE 让复杂查询更易读：每个 CTE 都有自己的名称和任务，主查询从上到下阅读起来像一个故事。PostgreSQL 还支持*递归* CTE（`WITH RECURSIVE`），这使你可以只用 SQL 遍历树、图和链。

## 本章要点 {#learn}

- 使用 `WITH name AS (...)` 定义 CTE
- 在一个查询中串联多个 CTE
- 数据修改 CTE：在 `WITH` 中使用 `INSERT`、`UPDATE`、`DELETE`
- 用递归 CTE 处理层级结构和图
- 性能方面的权衡（`MATERIALIZED` / `NOT MATERIALIZED`）

## 示例 {#example}

```sql {num="38.1" caption="公用表表达式（WITH）"}
-- A single CTE
WITH spend AS (
  SELECT customer_id, sum(total) AS total_spent
  FROM orders_example
  GROUP BY customer_id
)
SELECT c.name, COALESCE(s.total_spent, 0) AS spent
FROM customers_example c
LEFT JOIN spend s ON s.customer_id = c.id
ORDER BY c.id;

-- Multiple CTEs, each named and easy to read
WITH per_customer AS (
  SELECT customer_id, sum(total) AS total_spent, count(*) AS order_count
  FROM orders_example
  GROUP BY customer_id
),
big_spenders AS (
  SELECT * FROM per_customer WHERE total_spent > 30
)
SELECT c.name, b.total_spent, b.order_count
FROM big_spenders b
JOIN customers_example c ON c.id = b.customer_id
ORDER BY b.total_spent DESC;

-- Data-modifying CTE: archive then return what was archived
DROP TABLE IF EXISTS orders_archive;
CREATE TABLE orders_archive (LIKE orders_example INCLUDING ALL);

WITH archived AS (
  DELETE FROM orders_example
  WHERE total < 20
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived
RETURNING id;

-- RECURSIVE CTE: walk an employee hierarchy
WITH RECURSIVE reports AS (
  -- anchor: top-level employees (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees_example
  WHERE manager_id IS NULL
  UNION ALL
  -- recursive step: rows whose manager appears in the previous level
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees_example e
  JOIN reports r ON e.manager_id = r.id
)
SELECT depth, name FROM reports ORDER BY depth, id;
```

CTE 就是一个命名子查询。你可以定义多个 CTE，用逗号分隔，而且每个 CTE 都可以引用前面定义的 CTE。按从上到下的顺序阅读，每一步都是一个清晰的逻辑单元。与深层嵌套的派生表集合相比，CTE 通常更容易理解，也更容易修改。

数据修改 CTE 是 PostgreSQL 的特色：带有 `RETURNING` 的 `INSERT`、`UPDATE` 或 `DELETE` 可以作为 CTE 的主体，其返回的行会流入下一条语句——整个过程以原子方式在一次往返中完成。上面展示的“从一张表删除，再插入另一张表”归档模式是典型示例。

递归 CTE 由 `UNION ALL` 组合成两个部分：一个生成初始行的*锚点*查询，以及一个引用 CTE 名称来添加更多行的*递归*查询。PostgreSQL 会持续迭代，直到不再产生新行。可以用它们处理层级结构（员工 → 经理）、图遍历（查找所有传递依赖）和序列生成器（数字、日期）。

## 运行示例 {#run}

运行方式（需要前面课程中的表）：

```text
$ psql -f source/cte.sql postgres
 name  | spent
-------+-------
 Alice | 80.00
 Bob   |  0.00
 Carol |  0.00
(3 rows)
 ...
 depth | name
-------+-------
     1 | Alice
     2 | Bob
     2 | Carol
     3 | Dave
     3 | Eve
(5 rows)
```

## 常见陷阱 {#pitfalls}

- 在 PostgreSQL 12 及更高版本中，一个只引用一次、无副作用的非递归 SELECT CTE 通常会折叠到其父查询中——规划器会像处理子查询一样处理它。如果你想要较早版本“始终物化”的行为，请写成 `WITH x AS MATERIALIZED (...)`。
- `WITH RECURSIVE` 同时接受 `UNION ALL` 和 `UNION`。`UNION` 会消除完整行的重复项，包括此前生成的行；`UNION ALL` 则会保留它们。根据递归方式及其终止条件作出选择。
- 不会收敛的递归 CTE（递归步骤持续添加行）会形成无限循环。查询可能无限运行、溢写到磁盘，或失败；`work_mem` 不是执行限制。拿不准时，请添加终止条件（例如 `WHERE depth < 100`）。
- `WITH` 中的数据修改 CTE 彼此之间的*求值顺序未指定*——不要依赖它们之间的副作用。主语句与它们共享同一快照，且主语句与这些 CTE 之间的修改顺序没有保证。应通过 `RETURNING` 传递数据，而不要依赖可见的副作用。

> [!TIP] 提示
> 当查询超过约 20 行，或包含两个以上派生表时，把它重构为命名 CTE 几乎总是更好。你甚至可以暂时保留一个 CTE，把另一个内联，看看代价来自哪里。

> [!NOTE] 动手练习
> 写一个 CTE 计算订单总额的平均值，然后在主查询中显示每个订单与该平均值的差值。然后试试用递归 CTE 生成 1 到 10 的数字（`WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n < 10) SELECT * FROM t;`）。

## 下载 {#downloads}

SQL 文件：[cte.sql](/examples/source/cte.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)