卷 II · 取数CH 10深度 10/24

CTE 与递归：查询里的积木

查询长了会变成一坨没人看得懂的嵌套子查询。WITH 把它拆成有名字的积木，一块块清清楚楚。而它的进阶形态 WITH RECURSIVE，能做一件普通 JOIN 做不到的事——把一棵树、一张图，一层层展开。

WITH可读性递归层级与图

WITH：给子查询起个名字

CTE（Common Table Expression，公用表表达式）最朴素的用途，是把一个子查询提出来、起个名字，让主查询能像用表一样用它：

WITH scifi AS (
  SELECT * FROM books WHERE genre = 'scifi'
),
in_stock AS (
  SELECT * FROM books WHERE stock > 0
)
SELECT s.title
FROM scifi s
JOIN in_stock i ON s.id = i.id;   -- 两块积木拼起来

好处纯粹是可读性：把一个复杂查询拆成几个命名步骤，从上往下读，一步生成一个中间结果，主查询把它们拼起来。比三层嵌套的子查询好读太多。同一个 CTE 还能被主查询引用多次——定义一次，用好几遍。

⚠ CTE 是不是「优化屏障」，PG 12 是分水岭

历史上 PostgreSQL 的 CTE 有个特殊行为：它是优化屏障（optimization fence）——规划器不会把 CTE 和外层查询揉在一起优化，而是老老实实先算完 CTE、再给外层用。这有时是好事（可控），有时是坏事（挡住了本可以下推的过滤条件，变慢）。PostgreSQL 12 起改了默认：只被引用一次、且无副作用的 CTE 会被内联（inline）进外层一起优化。想强制老行为就写 WITH x AS MATERIALIZED (...)，想强制内联写 AS NOT MATERIALIZED。这是个能实实在在影响性能的细节，卷 V 会再提。

RECURSIVE：当数据是一棵树

有些数据天生是层级的：员工汇报给经理、经理汇报给总监；一条评论回复另一条评论；一个零件由多个子零件组成。这类结构存在表里，通常是一列指向同表另一行（mgr_id → id）。

第 7 章的自连接只能查一层（员工和他的直接经理）。想查「CEO 底下所有层级的人」，层数事先不知道，就得递归。WITH RECURSIVE 的结构永远是三段：

WITH RECURSIVE tree AS (
    -- ① anchor（锚）：递归的起点，先放进一批种子行
    SELECT id, name, 0 AS depth
    FROM emp WHERE mgr IS NULL          -- 从根（CEO）开始

  UNION ALL

    -- ② 递归项：拿「上一轮的结果」去找下一批
    SELECT e.id, e.name, t.depth + 1
    FROM emp e
    JOIN tree t ON e.mgr = t.id         -- 引用自己！这是递归的关键
)
-- ③ 主查询：用最终攒齐的整棵树
SELECT * FROM tree ORDER BY depth, id;

魔法在第二段：CTE tree 的定义里引用了 tree 自己。PostgreSQL 会这样跑它：

  1. 先跑 anchor，得到种子行，放进「工作表」。
  2. 反复跑递归项：拿上一轮新增的行去 JOIN，找出下一批行，加进结果。
  3. 直到某一轮一个新行都找不到，停。
▶ 动手 · 一层层展开组织架构

下面这台真引擎把递归的每一轮迭代拆开。点「下一次迭代」，看工作表怎么从根开始、一层层往下长（绿色是这一轮新加的行），直到找不到新人为止。

这棵 6 人的组织树，展开过程是 demo 当场跑的：迭代 0 放进根 CEO（1 行）；迭代 1 找到 CEO 的直接下属 VP-Eng、VP-Sales（2 行）；迭代 2 找到这两位 VP 的下属（3 行）；迭代 3 再找，一个新人都没有了——停。总共 3 次有效迭代、6 行、3 层。这就是「定点迭代」：反复施加同一个规则，直到结果不再变化。

◆ 递归 CTE 能解决的一整类问题

只要你的问题能描述成「从一批起点出发，反复应用同一个『找下一批』的规则，直到没有新的」，就是递归 CTE 的活：

  • 层级：组织架构、评论楼中楼、分类目录、文件夹树。
  • 物料清单（BOM）：一个产品由哪些零件组成，零件又由哪些子零件组成。
  • 图的可达性：从 A 城出发，坐火车能到哪些城市（当心环，用一个已访问集合去重防死循环）。
  • 生成序列：WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n < 10) 生成 1..10（不过 PG 有更趁手的 generate_series(1,10)）。
⇄ 对照 MySQL
  • CTE 和递归 CTE 都是 MySQL 8.0（2018 年）才有的。在那之前，MySQL 处理层级数据极其痛苦——要么在应用里递归查询（N 次往返数据库），要么用「路径枚举」「嵌套集」等设计模式绕开递归。PostgreSQL 从 8.4（2009 年）就有 WITH RECURSIVE，早了将近十年。
  • MySQL 8.0 之后语法与 PostgreSQL 基本一致（同为标准 SQL），这一处也拉平了。
  • 优化屏障 / 内联的细节两家不同：PostgreSQL 12+ 默认内联单次引用的 CTE 并提供 MATERIALIZED 关键字精确控制；MySQL 的 CTE 优化策略是另一套，迁移涉及性能敏感的 CTE 时值得单独 EXPLAIN 对一下。

这一章的一句话

WITH 把长查询拆成有名字的积木；WITH RECURSIVE 用「anchor + 反复拿上一轮的新行找下一批、直到没有新的」展开一棵树或一张图——层级、BOM、图可达全靠它。

卷 II 到此收尾，你会的 SQL 都被挖深了一层。接下来的卷 III，我们转向「当真」的第一根支柱——类型与约束：数据库怎么保证你存进去的每个值，都名副其实。