30. CTE 公用表表达式（WITH）

本节目标：学完本章你能用 WITH 给”中间查询结果”起个名字、让复杂查询变清爽，并理解递归 CTE 能做什么（如生成序列、遍历层级数据）。

前面我们在子查询那章说过：当 SQL 嵌套很深时，可读性会迅速下降，比如 FROM 里套 FROM、WHERE 里又套 SELECT。有没有办法把这些”中间结果”像变量一样先算好、起个名字，再在后面引用？有，这就是 CTE（Common Table Expression，公用表表达式），用 WITH 关键字开头。你可以把它理解成”只在这一条语句里生效的临时命名视图”。

本章仍用统一示例表 users / orders 举例，并补充两个经典场景（生成数字序列、遍历上下级）来说明递归 CTE。

为什么要用 CTE

回顾第 28 章”每个用户总消费额，再求平均”的例子，当时我们用 FROM 子查询写成：

SELECT AVG(per_user.total)
FROM (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
) AS per_user;

逻辑没错，但括号一层套一层，读起来要”从里往外”啃。用 CTE 改写后，思路变成”先算一步、再算一步”，顺着读就行：

sqlite> WITH per_user AS (
   ...>   SELECT user_id, SUM(amount) AS total
   ...>   FROM orders
   ...>   GROUP BY user_id
   ...> )
   ...> SELECT AVG(total) AS avg_user_spend
   ...> FROM per_user;

WITH per_user AS (...) 定义了一个叫 per_user 的临时结果集，后面主查询直接 FROM per_user 引用它。CTE 只在紧接着的那条语句里存在，语句结束就消失，不会真的在数据库里建表，所以又叫”一次性视图”。

Note

CTE 和子查询表达能力基本等价，它主要价值在可读性和可复用：同一个 CTE 可以在主查询里被多次引用，而不用把同一段子查询复制多遍；逻辑步骤也能被”命名”，读代码的人一看名字就懂这一步在算什么。

一次定义多个 CTE

一条 WITH 里可以用逗号分隔，定义多个 CTE，后面的 CTE 还能引用前面定义过的 CTE，像搭积木一样层层推进。

sqlite> WITH big_spenders AS (
   ...>   SELECT user_id, SUM(amount) AS total
   ...>   FROM orders
   ...>   GROUP BY user_id
   ...>   HAVING SUM(amount) > 1000
   ...> ),
   ...> spender_names AS (
   ...>   SELECT u.name, b.total
   ...>   FROM big_spenders AS b
   ...>   JOIN users AS u ON u.id = b.user_id
   ...> )
   ...> SELECT * FROM spender_names ORDER BY total DESC;

这里第一步 big_spenders 筛出”累计消费超 1000 的用户”，第二步 spender_names 把它和 users 连接补全名字，最后主查询排序输出。三步清清楚楚，比层层嵌套的子查询好读太多。

CTE 与子查询、临时表的关系

和派生表（FROM 子查询）：能力几乎一样，但 CTE 能命名、能复用、能引用前面的 CTE，结构更清楚；
和视图（VIEW）：视图是”持久保存在库里的命名查询”，CTE 是”只在这一句里活着”，用完即弃，不占数据库对象；
和临时表：临时表是真把数据物化到磁盘/内存里，CTE 通常只在语句执行期间存在、由优化器决定是否物化，更轻量。

Tip

实用技巧：当你发现一条 SQL 里同一个子查询出现了两三次，或者嵌套超过两层，就该考虑改写成 CTE。先想清楚”这一步算什么、下一步算什么”，给每一步起个好名字，代码质量和可维护性都会明显提升。调试时也可以把主查询暂时换成 SELECT * FROM 某个_cte 单独看中间结果。

递归 CTE 简介

CTE 还有一个”进阶形态”：递归 CTE（Recursive CTE），在 WITH 后加 RECURSIVE 关键字。它能处理”自己引用自己”的问题，典型用途有两个：生成一串连续数字/日期、遍历树形/层级数据（如员工—上级链）。

递归 CTE 由两部分用 UNION 或 UNION ALL 连接：

锚点（anchor）：初始的、非递归的那部分，提供起点；
递归体（recursive part）：引用 CTE 自身，不断基于上一步结果产生下一步，直到不再有新行。锚点和递归体之间必须用 UNION 或 UNION ALL 运算符连接（两者都合法，区别见下方 WARNING）。

生成 1 到 5 的数字序列：

sqlite> WITH RECURSIVE cnt(x) AS (
   ...>   SELECT 1
   ...>   UNION ALL
   ...>   SELECT x + 1 FROM cnt WHERE x < 5
   ...> )
   ...> SELECT x FROM cnt;

执行过程：锚点先产出 1；递归体基于 cnt 当前行 x 算出 x+1，只要 x<5 就继续；于是依次产出 2、3、4、5，到 x=5 时 x<5 不成立，停止。最终得到 1~5。这种”造测试数据、补缺失日期”的能力很实用。

遍历上下级链（沿用第 28 章的 employees 表）：从某个员工出发，一路向上找他的各级上级：

sqlite> WITH RECURSIVE chain AS (
   ...>   SELECT id, name, manager_id FROM employees WHERE id = 7   -- 锚点：从 7 号员工出发
   ...>   UNION ALL
   ...>   SELECT e.id, e.name, e.manager_id
   ...>   FROM employees AS e
   ...>   JOIN chain ON e.id = chain.manager_id                     -- 递归：chain 的上级
   ...> )
   ...> SELECT * FROM chain;

锚点取出 7 号本人，递归体每次用 chain.manager_id 去 employees 里找他的上级，一层层往上，直到某人的 manager_id 为 NULL（没有更上级）自然终止。

Warning

常见坑：递归 CTE 必须有”终止条件”，否则会无限循环、撑爆内存。终止靠三点：一是递归体里的 WHERE 条件最终会不满足（如 x < 5）；二是当某一轮递归体产不出新行时，递归自动停止；三是若用 UNION（而非 UNION ALL），重复行会在入队前被丢弃，对出现环的数据（如上下级关系成环）反而能自动去重、帮助终止。关于 UNION 与 UNION ALL 的选择：二者都合法，但 UNION 要做去重、更慢；UNION ALL 更快、却不做去重，必须靠 WHERE 等条件保证终止。官方建议：只要递归有上界，优先用 UNION ALL；担心写错导致无限循环，可在最外层加一个 LIMIT 作为安全阀，例如 SELECT x FROM cnt LIMIT 1000;。

CTE 能搭配什么

CTE 定义的是一个结果集，后面主查询里可以像普通表一样对它做 SELECT、JOIN、配合 WHERE/GROUP BY/ORDER BY，也可以把 CTE 和 JOIN、UNION、子查询自由组合。换句话说，前几章学的所有查询能力，套上 CTE 都照样用，只是结构更清晰。

CTE 会不会真的”物化”

一个常见疑问：CTE 定义在前面，主查询多次引用它，SQLite 会不会把它的结果算一次存起来复用？答案是：不一定。CTE 在语义上是”每次被引用时都重新算”，但查询优化器很聪明，如果它判断”物化（把中间结果存下来）更快”，就会自动这么做；如果它判断”直接内联展开更高效”，也可能把 CTE 展开成等价子查询。所以你写 CTE 主要为了人和维护者的可读性，性能交给优化器，通常不必担心”多算一遍”。这也是为什么鼓励用 CTE 替代又深又绕的子查询——既清爽，又不会天然变慢。

一个综合实战：分层统计

把前面学的串起来：先用 CTE 算出”每个用户的总消费”，再用第二个 CTE 按消费档位打标签，最后统计各档人数。注意这里完全是单表上的多步加工：

sqlite> WITH per_user AS (
   ...>   SELECT user_id, SUM(amount) AS total
   ...>   FROM orders GROUP BY user_id
   ...> ),
   ...> tagged AS (
   ...>   SELECT user_id, total,
   ...>     CASE
   ...>       WHEN total >= 1000 THEN '高价值'
   ...>       WHEN total >= 300  THEN '中价值'
   ...>       ELSE '普通'
   ...>     END AS level
   ...>   FROM per_user
   ...> )
   ...> SELECT level, COUNT(*) AS 人数
   ...> FROM tagged GROUP BY level;

per_user 算消费额，tagged 打档位标签，主查询汇总——三步各管一摊，比塞进一个超长 SQL 好读也好改。CASE 表达式在这里做”按阈值分类”，是配合 CTE 做分层统计的常用套路。

递归 CTE 的更多可能

除了造数字序列、爬上下级，递归 CTE 还能做”日期序列补全”（补出某个月每一天，再 LEFT JOIN 订单表找缺失日）、“路径展开”（如文件夹树、分类树从根到叶的全路径）。它们的骨架都一样：锚点给起点，递归体引用自身一步步推进，WHERE 或数据自然终止。记住这条骨架，遇到”层级 / 连续 / 递推”类问题，第一时间想到 WITH RECURSIVE。

类比小结

把 CTE 想成”给中间计算结果贴标签”：

普通子查询像”在括号里默默算”，读的人得钻进去才懂；
CTE 像”先把这一步命名成 per_user、big_spenders，再往下走”，步骤一目了然；
递归 CTE 则是在 CTE 里”让结果自己生自己”，适合造序列、爬层级。

掌握 WITH，你的复杂查询就从”一团括号”升级成”有目录的步骤表”。下一章我们看 ALTER TABLE，学习怎么在表建好之后修改它的结构。