如何用 SQL CTE 替代多层嵌套查询

Nora

发布时间：2026-08-23| 3人浏览过

原创

前言

很多 SQL 在功能上能跑通，但一旦嵌套层数变深，后续维护就会迅速变得吃力。CTE 的价值不在于“把所有子查询都改成 WITH”，而在于把重复逻辑、复杂聚合和多层依赖拆成可命名、可复用的步骤；看完这篇，你可以判断哪些嵌套值得替换、三层结构该怎么拆，以及在 MySQL 8.0+ 里如何避开递归与性能方面的常见坑。

哪些嵌套子查询值得改成 CTE

先说结论：CTE 不能直接替换所有嵌套子查询。它更适合那些需要重复引用、需要分步表达逻辑，或者已经嵌套得难以阅读的查询。

CTE 适用判断信息图，概括哪些嵌套子查询值得改写成 CTE — **CTE 改写前的适用性判断**先判断是否值得改，再决定是否引入 WITH，能避免把简单查询机械改写得更啰嗦。

例如下面这种结构，通常就比单纯一层派生表更值得考虑改写：

SELECT * FROM (SELECT ... FROM ...) t WHERE t.col > (SELECT AVG(...) FROM ...)

但如果只是单层派生表，例如 FROM (SELECT ...) t，而且逻辑本身很短、只用一次，强行改成 WITH 未必有收益，反而只是多了一层命名成本。

三个最值得替换的判断标准

同一子查询在主查询中出现至少 2 次，例如多次 JOIN 或在 WHERE 中重复使用。
嵌套深度达到 3 层或更多，缩进已经明显影响阅读。
子查询里包含聚合、JOIN、复杂过滤，拆出来后可以明确命名语义，例如 active_users、q3_sales。

判断标准其实很简单：如果把子查询单独命名之后，查询意图更清楚，那通常就值得改。

三层嵌套查询怎么拆成 CTE

三层嵌套改写的核心不是“从外往里包”，而是从最内层开始向上拆。每一层独立成一个 CTE，CTE 之间用逗号分隔，后一个 CTE 可以直接引用前一个。

三层嵌套改写为多个 CTE 的流程图，展示从最内层开始拆分并在主查询中直接引用 — **从最内层开始拆分 CTE**把最内层逻辑先命名，再逐层向上组织，是三层嵌套改写成 CTE 时最稳妥的顺序。

先看原始写法：

SELECT u.name, o.total
FROM users u
JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.id IN (
SELECT user_id FROM order_items WHERE status = 'shipped'
);

改成 CTE 后可以写成：

WITH shipped_users AS (
SELECT DISTINCT user_id FROM order_items WHERE status = 'shipped'
),
user_orders AS (
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id
)
SELECT u.name, o.total
FROM users u
JOIN user_orders o ON u.id = o.user_id
WHERE u.id IN (SELECT user_id FROM shipped_users);

改写时要抓住的几个要点

shipped_users 和 user_orders 都是并列定义的独立 CTE，本身不再继续嵌套。
主查询里直接使用 CTE 名称，不需要再额外加括号包一层派生表。
如果后续还要统计发货用户数，可以直接复用：SELECT COUNT(*) FROM shipped_users。

这类改写最直接的收益，是把“筛出发货用户”和“汇总订单金额”变成两个可读的业务步骤，而不是都塞在主查询里临时展开。

MySQL 8.0+ 使用递归 CTE 时要注意什么

在 MySQL 8.0+ 里，递归 CTE 不是只要写出递归结构就能执行，必须显式加上 RECURSIVE 关键字。这个要求和 PostgreSQL、SQL Server 的使用习惯不同，很多报错都出在这里。

MySQL 递归 CTE 与性能注意点对照图，展示 RECURSIVE、ERROR 1222、物化和作用域限制 — **递归写法与执行代价的关键检查项**递归 CTE 和普通 CTE 的问题点不同，前者容易写错结构，后者更常见的是作用域和重复计算。

典型报错示例是：

ERROR 1222 (21000): The used SELECT statements have a different number of columns

正确写法应当像下面这样：

WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.level + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

递归 CTE 的几个常见坑

漏写 RECURSIVE，会直接触发语法相关错误。
基础查询和递归查询的列数或类型不一致，会报 ERROR 1222。
没有设置终止条件，例如 level < 10，可能导致无限递归或内存问题。
递归引用时写成 JOIN org_tree 而不是 JOIN org_tree t，容易因为别名缺失导致报错。

如果你在 MySQL 里调试递归 CTE，优先检查的就是这四项，通常比盲目改业务逻辑更有效。

CTE 的作用域和性能影响

CTE 不是临时表，更准确地说，它是当前查询计划中的一个命名节点。也正因为如此，它的可读性优势很明显，但性能表现不一定总是优于原始嵌套写法。

一个容易被忽略的事实是：CTE 在执行时仍然可能被重复计算，是否物化取决于数据库优化器策略。

在一条语句里写 SELECT * FROM cte1; SELECT * FROM cte1; 这种理解并不准确；CTE 的作用域仅限于它所在的那一条语句。
如果同一个 cte1 在查询中被 JOIN 两次，而且数据量较大，MySQL 可能重复扫描源表。
MySQL 8.0 默认不物化，PostgreSQL 12+ 默认物化，这会直接影响重复引用时的执行代价。
MySQL 8.0.25+ 如果希望强制物化，可在 CTE 定义前使用提示：/*+ MATERIALIZE */。