# CTE · 让一条 SQL 学会「递归」
> WITH 子句把复杂查询拆成命名的中间步骤；WITH RECURSIVE 让 SQL 能自我引用，一条语句遍历树、生成序列；而「改写型 CTE」能在 WITH 里跑 INSERT/UPDATE/DELETE 再 RETURNING 给下一步——这是 PostgreSQL 的独门绝技。
- 作者：David（道雾轩）
- 专栏：PostgreSQL 特性详解（https://daiw.org/manual/postgresql.md）
- 最后更新：2026-07-13
- 原文：https://daiw.org/manual/postgresql/cte
- 转载与引用：请注明出处并附原文链接（https://daiw.org/about/copyright）
# CTE · 让一条 SQL 学会「递归」
`WITH` 子句（公用表表达式，Common Table Expression）第一眼像是「给子查询起个名字」的语法糖，但它其实藏着三样很不一样的能力：**拆解复杂查询**、**递归遍历**、以及**在查询里改数据**。第三样，是 PostgreSQL 少有人知却极其好用的独门功夫。
## 普通 CTE：把复杂查询拆成人能读的步骤
最基础的用法，是把一个大查询拆成几个命名的中间结果，像写代码时拆函数：
```sql
WITH top_sales AS (
SELECT name FROM emp WHERE dept = 'Sales' ORDER BY amount DESC LIMIT 3
), top_others AS (
SELECT name FROM emp WHERE dept <> 'Sales' ORDER BY amount DESC LIMIT 3
)
SELECT name FROM top_sales UNION ALL SELECT name FROM top_others;
-- -> ['Carol', 'Alice', 'Frank', 'Dave']
```
嵌套三层的子查询，换成 `WITH` 一字排开，可读性天差地别。
一个影响性能的历史细节：PG 12 **之前**，CTE 是「优化栅栏」——每个 `WITH` 块都被强制**物化**（算出完整中间结果再给下一步用），优化器不能把条件下推进去。PG 12 **起**改为默认**内联**（只被引用一次、无副作用的 CTE 会被展开优化），你也能用 `WITH x AS MATERIALIZED (...)` 或 `AS NOT MATERIALIZED` 手动指定。所以在老版本上，别以为 `WITH` 只是语法糖——它可能悄悄改变执行计划。
## 递归 CTE：一条 SQL 遍历树
`WITH RECURSIVE` 让 CTE 能**引用它自己**，于是能表达循环 / 递归。结构永远是两段，用 `UNION ALL` 连起来：
```sql
WITH RECURSIVE fib(n, a, b) AS (
SELECT 1, 0, 1 -- 锚点:递归的起点
UNION ALL
SELECT n + 1, b, a + b FROM fib WHERE n < 10 -- 递归项:引用自己
)
SELECT n, a FROM fib;
-- -> [(1,0), (2,1), (3,1), (4,2), (5,3), (6,5), (7,8), (8,13), (9,21), (10,34)]
```
递归 CTE 的执行是**迭代**的，不是真的函数递归：PostgreSQL 维护一张「工作表」，先放入锚点结果，然后反复执行递归项——每一轮把上一轮**新产生的行**当输入，直到某一轮不再产生新行为止。理解这一点，你就知道为什么递归项里的 `WHERE n < 10` 是**终止条件**：少了它，或图里有环，递归就停不下来。
真正的用武之地是**树 / 图遍历**。比如「找出 Alice 的所有下属（含各级）」：
```sql
WITH RECURSIVE subs AS (
SELECT id, name FROM emp WHERE name = 'Alice' -- 从 Alice 出发
UNION ALL
SELECT e.id, e.name FROM emp e JOIN subs s ON e.manager_id = s.id -- 逐层往下钻
)
SELECT name FROM subs;
-- Alice → Bob → Carol → Dave → Eve → Frank(整棵子树)
```
组织架构、评论楼中楼、物料清单(BOM)展开、分类目录——凡是「父子关系存在一张表里」的结构，一条递归 CTE 就能整棵展开，不用在应用层写循环反复查库。
## 改写型 CTE：在查询里改数据（PG 独门）
这是最容易被忽略、却最有价值的一招：**`WITH` 块里可以跑 `INSERT` / `UPDATE` / `DELETE`，用 `RETURNING` 把受影响的行交给后续步骤**。于是「从一张表删、往另一张表写」能在**一条原子语句**里完成：
```sql
WITH moved AS (
DELETE FROM active WHERE status = 'done' RETURNING *
)
INSERT INTO archived SELECT * FROM moved;
-- active 剩 2 行 / archived 拿到 2 行,一条 SQL 原子完成 ✓
```
删除和归档在**同一个语句、同一个快照**里发生，不会出现「删了但没归档成功」的中间态。做「软删除转历史表」「拆分订单」「审计双写」这类操作，这比开事务写两条语句更简洁、更不易出错。
改写型 CTE 有个反直觉的规矩：**所有子语句看到的是同一个开始快照**，`WITH` 块之间的执行顺序不保证。所以别指望「先 UPDATE 的 CTE 改了数据，后面的 SELECT 能看见」——它们看到的都是语句开始时的数据。要有先后依赖，就用 `RETURNING` 把数据显式传下去，而不是靠副作用。
## 横向对比
| 能力 | PostgreSQL | MySQL 8.0+ | Oracle | SQL Server |
| --- | --- | --- | --- | --- |
| 普通 CTE | 有 | 有 | 有 | 有 |
| 递归 CTE | 有（SQL 标准） | 有 | `CONNECT BY` 或递归 CTE | 有 |
| CTE 内联优化 | PG 12+ 可控 | 有 | 有 | 有 |
| **改写型 CTE + RETURNING** | **有，链式** | ❌ 不支持 | ❌ 不支持 | 有限(`OUTPUT`) |
递归 CTE 如今是标配（Oracle 老式的 `CONNECT BY` 也能做树遍历）。但**在 `WITH` 里 `DELETE ... RETURNING` 再喂给 `INSERT`** 这种链式改写，是 PostgreSQL 明显领先的地方——MySQL 至今做不到。
## 应用场景
- **层级遍历**：组织架构、评论树、地区树、BOM 物料展开——递归 CTE 一条搞定。
- **序列 / 日历生成**：递归 CTE（或 `generate_series`）造出连续日期、补齐时间序列的空洞。
- **原子搬数据**：删除 + 归档、拆单、状态迁移双写，改写型 CTE 保证原子。
- **复杂报表**：多个中间聚合用 `WITH` 串起来，可读性远胜嵌套子查询。
## 小结与下一章
CTE 把「拆解」「递归」「原子改写」三件事收进一个 `WITH` 语法里。递归 CTE 让 SQL 有了遍历树和图的能力，改写型 CTE 则让「查」和「改」能在一条语句里协作——后者是 PostgreSQL 相对 MySQL 的一个实打实的优势。
下一章的窗口函数，解决另一类「以为得写代码」的需求：**排名、环比、累计、每组 Top N**——不合并行，却能算出每行「在它那一组里的位置」。👉 [窗口函数：不合并行的分组统计](https://daiw.org/manual/postgresql/window-functions)。
## 参考文献
- [1] PostgreSQL 官方文档.「7.8. WITH Queries (Common Table Expressions).」https://www.postgresql.org/docs/current/queries-with.html —— 普通 / 递归 / 改写型 CTE 与物化控制。
- [2] PostgreSQL 官方文档.「7.8.4. Data-Modifying Statements in WITH.」https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING —— 改写型 CTE 的快照语义。