CTE · 让一条 SQL 学会「递归」 · PostgreSQL 特性详解

WITH 子句把复杂查询拆成命名的中间步骤；WITH RECURSIVE 让 SQL 能自我引用，一条语句遍历树、生成序列；而「改写型 CTE」能在 WITH 里跑 INSERT/UPDATE/DELETE 再 RETURNING 给下一步——这是 PostgreSQL 的独门绝技。

CTE · 让一条 SQL 学会「递归」

WITH 子句（公用表表达式，Common Table Expression）第一眼像是「给子查询起个名字」的语法糖，但它其实藏着三样很不一样的能力：拆解复杂查询、递归遍历、以及在查询里改数据。第三样，是 PostgreSQL 少有人知却极其好用的独门功夫。

一个影响性能的历史细节：PG 12 之前，CTE 是「优化栅栏」——每个 WITH 块都被强制物化（算出完整中间结果再给下一步用），优化器不能把条件下推进去。PG 12 起改为默认内联（只被引用一次、无副作用的 CTE 会被展开优化），你也能用 WITH x AS MATERIALIZED (...) 或 AS NOT MATERIALIZED 手动指定。所以在老版本上，别以为 WITH 只是语法糖——它可能悄悄改变执行计划。

递归 CTE：一条 SQL 遍历树

WITH RECURSIVE 让 CTE 能引用它自己，于是能表达循环 / 递归。结构永远是两段，用 UNION ALL 连起来：

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 的所有下属（含各级）」：

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 把受影响的行交给后续步骤。于是「从一张表删、往另一张表写」能在一条原子语句里完成：

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——不合并行，却能算出每行「在它那一组里的位置」。👉 窗口函数：不合并行的分组统计。

参考文献

[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 的快照语义。

CTE · 让一条 SQL 学会「递归」