SQL 数据分析 · 指南 · 2026年10月30日 · 13 分钟阅读

SQL CTE（WITH 用法）：把查询拆成一步一步好读的写法

CTE（common table expression，公用表表达式）是一个有名字的临时结果，只在一条查询里存在。你用 WITH name AS (SELECT ...) 定义它，然后在主查询里像用表一样使用 name。底层并没有发生什么子查询做不到的事。它的意义在于可读性：不用把查询嵌套三层，而是从上往下写，每个 CTE 一步，并给每一步起一个能说明内容的名字。

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ... FROM step_two;

下面的例子都在 Nomto SQL 课程使用的航空公司数据库上运行：2026 年夏季 104 个机场之间的 33,121 个航班，以及订单、机票、座位和会员计划数据。SQL 采用 PostgreSQL 语法，展示的每个结果都是真实运行得到的。

第一个 CTE

会员通常有几张机票？这需要两层聚合：先算每个会员的机票数，再算每种机票数各有多少会员。用 CTE 写，每一层各占一个代码块：

WITH per_member AS (
  SELECT member_id, COUNT(*) AS tickets
  FROM ticket_members
  GROUP BY member_id
)
SELECT tickets, COUNT(*) AS members
FROM per_member
GROUP BY tickets
ORDER BY tickets
LIMIT 8;

per_member 里每个有机票的会员占一行，主查询像读表一样读它。完全相同的逻辑也可以写成派生表（FROM (SELECT ...) AS per_member），只有一步时这样写也没问题。步骤一多，差别就显出来了。

（数字一路下降之后，在 7 张和 8 张机票处突然回升，这是数据本身的特点，不是查询写错了。在有人拿它画图之前值得仔细看看，而这类两步查询往往正是你发现这种现象的方式。）

多个 CTE：一个想法一步

按机型统计，已到达航班的平均客座率（乘客数除以座位数）是多少？需要三样材料：每种机型的座位数、每个航班的乘客数，以及航班本身。每样各做成一个 CTE，后面的 CTE 可以使用前面的：

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats
  FROM seats
  GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax
  FROM ticket_flights
  GROUP BY flight_id
),
flight_load AS (
  SELECT f.flight_id,
         f.aircraft_code,
         COALESCE(p.pax, 0) AS pax,
         s.seats,
         COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
  LEFT JOIN passengers p ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
)
SELECT aircraft_code,
       COUNT(*) AS flights,
       MAX(seats) AS seats,
       ROUND(AVG(pax), 1) AS avg_pax,
       ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC;

每个 CTE 只做一件事，最后的 SELECT 只负责汇总。在连接之前先把座位和乘客聚合好，也是保证数字正确的关键：如果把 flights 直接连接原始的 seats 和 ticket_flights，行数会成倍增加，每个计数都会被放大。

下面是用嵌套派生表写的同一个查询（只取前三行）：

SELECT aircraft_code, COUNT(*) AS flights, ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM (
  SELECT f.aircraft_code, COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN (SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code) s
    ON s.aircraft_code = f.aircraft_code
  LEFT JOIN (SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id) p
    ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
) AS flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC
LIMIT 3;

答案一样。但你得从里往外读，而且每一步都没有名字。代码评审时，说「检查一下 passengers」比说「检查第一个子查询里面的第二个子查询」容易得多。

一步一步调试 CTE 链

有名字的步骤真正的好处是：把最后的 SELECT 换掉，就能查看任意一步。换成 SELECT * FROM flight_load LIMIT 20 或者一个计数，你就能准确看到这一步产出了什么。

这个习惯能抓住下面这类错误。把 LEFT JOIN passengers 改成普通的 JOIN，客座率一下子就跳上去了：

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id
)
SELECT f.aircraft_code,
       COUNT(*) AS flights,
       ROUND(100 * AVG(p.pax * 1.0 / s.seats), 1) AS avg_load_pct
FROM flights f
JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
JOIN passengers p ON p.flight_id = f.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.aircraft_code
ORDER BY avg_load_pct DESC;

塞斯纳的客座率从 14% 变成了 50%，因为它的 3,328 个空航班被悄悄丢掉了。快速检查一下 passengers 这一步就能看出原因：16,707 个已到达航班里，只有 11,438 个有机票。内连接只保留这些航班。两个版本单独看都不能说是「唯一正确」的答案，但你必须清楚自己在回答哪个问题，而有名字的步骤让这种检查只需要一行。

多次引用同一个 CTE

主查询可以多次引用同一个 CTE，派生表则只能复制一份才能做到。经典用法：一个值和它占总数的比例。

WITH route_revenue AS (
  SELECT f.departure_airport || '-' || f.arrival_airport AS route,
         SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.status = 'Arrived'
  GROUP BY route
)
SELECT route,
       ROUND(revenue) AS revenue,
       ROUND(100 * revenue / (SELECT SUM(revenue) FROM route_revenue), 2) AS pct_of_total
FROM route_revenue
ORDER BY revenue DESC
LIMIT 5;

route_revenue 用了两次：一次作为主表，一次在算总数的标量子查询里。451 条航线的已到达航班合计带来 1.143 亿美元收入，排第一的多莫杰多沃至哈巴罗夫斯克航线占了将近 4%。

CTE 也可以放一行汇总数据，再连接到每一行上。和平均水平相比，哪些天的订单最多？

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  GROUP BY day
),
stats AS (
  SELECT AVG(bookings) AS avg_bookings FROM daily
)
SELECT d.day, d.bookings, ROUND(d.bookings / s.avg_bookings, 2) AS vs_avg
FROM daily d
CROSS JOIN stats s
ORDER BY d.bookings DESC
LIMIT 5;

stats 读取 daily，主查询同时读取两者。（平均每天 4,692.6 笔订单，是 6 月 17 日到 8 月 11 日共 56 天的平均值；最后一天的数据不完整，会把平均值稍微拉低。做这类比较时，通常要把不完整的日子去掉。）

给 CTE 的列命名

可以在 CTE 名字后面列出列名，它们会覆盖内层查询里的列名：

WITH model_seats (code, seat_count) AS (
  SELECT aircraft_code, COUNT(*)
  FROM seats
  GROUP BY aircraft_code
)
SELECT code, seat_count
FROM model_seats
ORDER BY seat_count DESC
LIMIT 3;

多数人更习惯在 SELECT 里起别名（COUNT(*) AS seat_count）。当内层查询里有未命名的表达式时，列名列表是个整洁的选择，而且 CTE 的「接口」在第一行就一目了然。

递归 CTE

WITH RECURSIVE 让 CTE 可以引用自己。它由 UNION 或 UNION ALL 连接的两部分组成：产生起始行的锚点部分，以及根据上一轮的行产生新行的递归部分。某一轮不再产生新行时，递归就停止。

最小的例子是生成数字：

WITH RECURSIVE n AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

WHERE i < 5 就是停止条件。没有它，查询永远不会结束。（生成数字和日期时，generate_series 更简单，见日期序列指南。）

递归真正的用武之地是图和层级结构。从南萨哈林斯克（UUS）出发，飞到航线网络里的其他机场需要几段航班？

WITH RECURSIVE routes AS MATERIALIZED (
  SELECT DISTINCT departure_airport AS src, arrival_airport AS dst
  FROM flights
),
reach AS (
  SELECT 'UUS'::VARCHAR AS airport, 0 AS hops
  UNION
  SELECT r.dst, reach.hops + 1
  FROM reach
  JOIN routes r ON r.src = reach.airport
  WHERE reach.hops < 4
),
best AS (
  SELECT airport, MIN(hops) AS hops
  FROM reach
  GROUP BY airport
)
SELECT hops, COUNT(*) AS airports
FROM best
GROUP BY hops
ORDER BY hops;

从 UUS 有 4 个直飞目的地，另外 76 个机场需要转机一次，最后 23 个需要转机两次。没有哪个机场需要飞第四段：搜索允许深入四跳，但三跳之后就再也找不到新机场了，所以在这份航班计划里，从 UUS 最多飞三段就能到达任何一个机场。

有四个细节让这个查询是安全的：

• WITH RECURSIVE 只在最上面写一次，尽管只有 reach 是递归的。普通 CTE（routes、best）可以放在同一个 WITH 里。
• 用的是 UNION（不是 UNION ALL），它会去掉重复的 (airport, hops) 行，当很多路径通向同一个机场时，可以防止行数爆炸。
• hops < 4 是硬性的深度上限。在有环的图上（航线是双向的），没有上限的递归可能永远跑下去。
• routes AS MATERIALIZED 让航线列表在递归开始前只计算一次。在 DuckDB（课程练习场使用的引擎）上，去掉它时同一个查询有时会提前停止，只返回前两行；加上 MATERIALIZED 结果就稳定了。PostgreSQL 也接受这个关键字。

CTE、子查询、临时表和视图的区别

一次分析内部的各个步骤，用 CTE。如果同一个中间结果要喂给几条独立的查询，用临时表可以省去重复计算。如果整个团队都需要这段逻辑，它应该放进视图或数据管道里的模型。

CTE 会让查询变快还是变慢？

默认情况下都不会。在 PostgreSQL 12 及以上版本里，只引用一次的非递归 CTE 会像子查询一样被内联进主查询，优化器能看穿它。被引用多次的 CTE 通常只计算一次，然后复用。

你可以改变这个选择。WITH big AS MATERIALIZED (...) 强制 PostgreSQL 只计算一次 CTE 并保存结果，当 CTE 开销很大、而优化器又反复重算它时会有帮助。NOT MATERIALIZED 强制内联，这样主查询里的筛选条件可以下推到 CTE 内部。DuckDB 也接受同样的 MATERIALIZED 和 NOT MATERIALIZED 关键字。

「CTE 是优化屏障」这个老说法来自 PostgreSQL 11 及更早版本，那时 CTE 总是被物化。如果你在旧博客里读到这句话，原因就在这里。

可移植性说明

• PostgreSQL、DuckDB、MySQL 8+、SQLite、BigQuery、Snowflake 都支持 WITH 和 WITH RECURSIVE。
• SQL Server 支持 CTE 和递归，但不用 RECURSIVE 这个词，直接写 WITH。另外，如果上一条语句没有以分号结尾，WITH 前面必须加分号，所以你会在 SQL Server 代码里看到 ;WITH。
• 修改数据的 CTE（WITH deleted AS (DELETE ... RETURNING *) SELECT ...）是 PostgreSQL 的特性。DuckDB 和大多数其他数据库只允许 CTE 里写 SELECT。
• 锚点部分里的 'UUS'::VARCHAR 类型转换，是为了让递归的两部分列类型一致。PostgreSQL 要求锚点和递归部分的类型完全匹配；可移植的写法是 CAST('UUS' AS VARCHAR)。

常见错误

1. 最后一个 CTE 后面多了逗号。 CTE 之间用逗号分隔，但最后一个 CTE 和主 SELECT 之间没有逗号。), SELECT ... 是语法错误。
2. 写了两次 WITH。 一条语句只有一个 WITH，其余 CTE 用逗号接在后面：WITH a AS (...), b AS (...)。
3. 以为 CTE 在语句结束后还在。 分号之后，这个名字就消失了。如果下一条查询也要用，就再写一遍 CTE，或者建一张临时表。
4. 依赖 CTE 里的 ORDER BY。 它不能决定最终结果的顺序。排序写在主查询里。
5. 递归 CTE 没有停止条件。 一定要限制深度，或者确保每一轮最终会不再返回新行。当路径会重复访问同样的行时，用 UNION 代替 UNION ALL 会有帮助。
6. 一长串 CTE 没人检查。 七个 CTE 只有在每一个都正确时才算好读。搭建链条时检查每一步的行数，尤其是在连接之后。

想看被 CTE 取代的那些子查询写法，可以读 SQL 子查询。很多分析用的 CTE 链最后都会接一个窗口函数；SQL 窗口函数详解和留存分析指南里有更长的真实链条。

常见问题

SQL 里的 CTE 是什么？

公用表表达式：用 WITH name AS (SELECT ...) 定义、供后面的查询使用的有名字的临时结果。它只在这一条语句里存在。

CTE 和子查询有什么区别？

逻辑上没有区别：只用一次的 CTE 和派生表的行为一样。区别在于实用层面：CTE 有名字，写在最上面，查询可以按顺序读下来；它能被引用多次，还能递归。

一个查询里能用多个 CTE 吗？

能。WITH 只写一次，CTE 之间用逗号分隔。每个 CTE 都可以使用在它之前定义的 CTE。

CTE 比子查询慢吗？

在当前的 PostgreSQL 和 DuckDB 里不会：只用一次的 CTE 会像子查询一样被内联。在 PostgreSQL 11 及更早版本里，CTE 总是被物化，可能会妨碍优化。需要控制时，用 MATERIALIZED 或 NOT MATERIALIZED。

递归 CTE 用来做什么？

处理层级和图：组织架构、分类树、机场之间的航线、事件链。它把一个查询反复作用在自己上一轮的输出上，直到不再出现新行。

其他语言： English, Español, Português (Brasil), Tiếng Việt