企业官网建设流程全解析

前段时间帮同事 review 一条慢查询 SQL，看到一段嵌套了整整三层的子查询，里面还重复引用同一段过滤条件。我当时的感受就一句话：这不是在写代码，这是在叠罗汉。后来我把它改写成两张 WITH 临时表的写法，SQL 从 80 行缩到 40 行，执行计划反而更清晰，问题排查一下子轻松了很多。

在 MySQL 8.0 及以上版本里，WITH 语法（官方叫 Common Table Expression，简称 CTE，公共表表达式）就是专门用来解决这类问题的。它本质上是一个命名的临时结果集，可以让你把复杂的子查询拆成一段一段有名字的模块，主查询再去引用这些模块。它的厉害之处不只是让 SQL 变短，而是让逻辑真正变得可读、可复用、可递归。

这篇文章我准备把 MySQL 中 WITH 的多种用法一次性讲透，从基础语法到递归查询，从和窗口函数配合到常见性能坑，全部用实际例子说话。不管你是刚接触 CTE 的初学者，还是已经写过不少业务 SQL 的开发者，这篇文章都能给你一些可以直接抄走的写法。

1. 为什么需要 WITH：从一段让人头疼的 SQL 说起

1.1 子查询嵌套带来的可读性灾难

先看一个几乎所有业务系统都会遇到的场景：统计每个部门的员工人数，而且只要人数大于 5 的部门。常规写法是这样：

SELECT d.dept_name, t.cnt FROM department d JOIN ( SELECT dept_id, COUNT(*) AS cnt FROM employee WHERE status = 1 GROUP BY dept_id ) t ON d.id = t.dept_id WHERE t.cnt > 5;

这段 SQL 只有两层，其实还好。但如果需求再复杂一点，比如还要关联考勤表、还要过滤掉最近三个月没有打卡记录的人、还要按部门主管排序，子查询就会一层套一层，很快就变成别人口中的“面条 SQL”。

遇到这种 SQL，后来的人要读懂它的逻辑，得从最内层开始一层层往外剥。更要命的是，如果同一条过滤条件在多个子查询里都要用到，你就得在每个子查询里复制一遍。等哪天业务逻辑变了，你得记得把每一处都改掉，漏一处就是数据错误。

1.2 WITH 是怎么解决这个问题的

WITH 的核心理念，就是把“临时结果集”从一个匿名的、嵌套的表达式，变成一个命名清晰、定义在语句开头的内容块。它的执行逻辑其实和子查询没什么两样，数据库还是会去执行那段子查询，但读代码的人不需要再层层往里钻了。

还是上面那个例子，用 WITH 改写：

WITH dept_cnt AS ( SELECT dept_id, COUNT(*) AS cnt FROM employee WHERE status = 1 GROUP BY dept_id ) SELECT d.dept_name, dc.cnt FROM department d JOIN dept_cnt dc ON d.id = dc.dept_id WHERE dc.cnt > 5;

对比一下：WITH 部分先把“统计各部门人数”这件事做成一个名字叫dept_cnt的临时结果集，后面主查询直接按名字引用。SQL 的阅读顺序从上到下，就是逻辑顺序从先到后，不会再出现“为了读懂外层，先得看懂内层”的逆天体验。

顺便说一句，很多人会纠结“CTE 和临时表有什么区别”“CTE 会不会比子查询慢”。我直接说结论：在 MySQL 8.0 的优化器里，CTE 的底层处理和派生表（即 FROM 子句里的子查询）非常类似，大多数情况下优化器会把它们转换成一模一样的执行计划。所以，写 CTE 不是因为性能一定更好，而是因为代码可读性和可维护性大幅提升，这才是它最核心的价值。

1.3 版本要求与基础环境确认

说到版本，这里必须划一个重点：MySQL 的 WITH 语法是 8.0 版本才正式引入的，如果你还在用 5.7 或者更早的版本，那就别想了，直接不支持。线上如果看到Syntax error near 'WITH'之类的报错，先别急着怀疑 SQL，先确认一下版本。

如果你用的是 MySQL 8.0 及以上版本，直接就能用。我自己一般用 Navicat 或者命令行客户端来跑示例，两个都没问题。这里顺便提一嘴，测试 CTE 最好的方式就是打开EXPLAIN ANALYZE（8.0.18+），能看到每段 CTE 到底是怎么执行的，后面讲性能排查的时候还会再提到。

2. WITH 基础语法：从单 CTE 到多 CTE 组合

2.1 最基础的单 CTE 用法

先看最简单的格式：

WITH cte_name AS ( -- 这里是一个完整的 SELECT 语句 SELECT ... ) SELECT ... FROM cte_name;

几点说明：

• WITH关键字后面是 CTE 的名字，最好起一个能表达语义的名字，比如active_users、monthly_sales，不要用a、b、tmp这种。
• AS后面的括号里就是 CTE 的内容，本质上是一段完整的 SELECT。
• 主查询可以是SELECT、INSERT、UPDATE、DELETE，都可以去引用 CTE。

来看一个相对接近业务的例子。假设要查“2024 年每个月的订单总额，并且要和上个月的订单总额做对比”。如果不加 CTE，你可能要搞两个子查询再 JOIN；有了 CTE，可以先定义月度汇总，再在外面做自关联：

WITH monthly_amount AS ( SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS total_amount FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' GROUP BY DATE_FORMAT(order_date, '%Y-%m') ) SELECT curr.month, curr.total_amount AS current_amount, prev.total_amount AS previous_amount, ROUND((curr.total_amount - prev.total_amount) / prev.total_amount * 100, 2) AS mom_growth FROM monthly_amount curr LEFT JOIN monthly_amount prev ON curr.month = DATE_ADD(prev.month, INTERVAL 1 MONTH) ORDER BY curr.month;

这个例子里，monthly_amount这个 CTE 被引用了两次。这里就体现出 CTE 的第二个优势：一个结果集可以在同一条语句里被多次引用，而派生表如果要复用，你得复制两遍，SQL 直接膨胀。

2.2 多 CTE：用逗号一次定义多个结果集

现实中的查询很少只依赖一个中间结果集。WITH 支持在一个语句里同时定义多个 CTE，用逗号分隔，后面的 CTE 还可以引用前面的 CTE。

WITH dept_summary AS ( SELECT dept_id, COUNT(*) AS emp_cnt FROM employee WHERE status = 1 GROUP BY dept_id ), dept_with_avg AS ( SELECT ds.dept_id, ds.emp_cnt, AVG(e.salary) AS avg_salary FROM dept_summary ds JOIN employee e ON ds.dept_id = e.dept_id GROUP BY ds.dept_id, ds.emp_cnt ) SELECT d.dept_name, dwa.emp_cnt, dwa.avg_salary FROM department d JOIN dept_with_avg dwa ON d.id = dwa.dept_id;

注意几个关键点：

• 第二个 CTEdept_with_avg引用了第一个 CTEdept_summary，这是完全允许的，这也是 CTE 比临时表更灵活的地方，临时表你得写多条语句，CTE 只需要一条 SQL。
• 多个 CTE 之间是逗号分隔，最后一个 CTE 的右括号后面没有逗号，直接跟主查询。
• 从业务语义上讲，dept_summary完成第一层统计，dept_with_avg在它的基础上做第二层加工，整个语句像搭积木一样，每一层职责清晰。

有人可能会问，这不就是把子查询换个地方放吗？确实，本质上它还是子查询，但关键在于“命名”和“分层”。一个没有名字的嵌套子查询，要读懂得靠猜；一个有名字的 CTE，读代码的人一眼就知道这段数据是什么。这个差异在长期维护的 SQL 里会被放大到极其明显。

2.3 CTE 与派生表、视图、临时表的选择对比

这里把我实际工作中对四类方案的选型心得整理成一张表，方便你对照：

我的建议很简单：如果中间结果集只需要在一条 SQL 里用，优先用 CTE；如果要跨多条 SQL 使用，或者中间结果集特别大、重复计算代价高，再考虑临时表；视图则适合把“基础宽表”或“通用过滤规则”沉淀下来，让业务层反复使用。

3. 递归 CTE：一行数据也能玩出树的形状

3.1 什么是递归 CTE，什么时候需要它

递归 CTE 是 WITH 语法里最让人眼前一亮的部分。它允许一个 CTE 引用自身，从而在一条 SQL 里实现循环或递归遍历。这在处理树形结构、层级关系、连续序列时非常有用。

典型场景包括：

• 组织结构树：从某个主管开始，往下查所有直属和间接下属。
• 商品分类树：查某个分类及其所有子分类下的商品。
• 无限级菜单：一次查出一个菜单及其全部子菜单。
• 生成长度不定的连续日期序列，比如补齐报表里缺失的日期。

在 MySQL 8.0 之前，这类需求往往要写存储过程或者程序代码去循环。有了递归 CTE，SQL 自己就能搞定，代码量少一大截。

3.2 递归 CTE 的语法结构

递归 CTE 的语法由两个部分通过UNION ALL（也可以UNION DISTINCT）拼起来：

WITH RECURSIVE cte_name AS ( -- 锚点：初始查询，递归的起点 SELECT ... UNION ALL -- 递归部分：引用 cte_name 自身 SELECT ... FROM cte_name WHERE ... ) SELECT * FROM cte_name;

两部分缺一不可：锚点查询负责生成第一轮数据；递归部分负责基于上一轮的结果继续查询，并且通过 WHERE 条件控制什么时候停下来。如果递归部分没有终止条件，MySQL 会报一个Recursive query aborted after 1001 iterations的错误，这是防爆机制在保护你。

3.3 实战示例一：生成数字序列

先看最直观的简单用法——生成从 1 到 100 的连续数字：

WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100 ) SELECT n FROM seq;

这段 SQL 的执行过程大致是：

1. 先执行锚点，得到第一行n = 1。
2. 递归部分基于n = 1，生成n = 2。
3. 基于n = 2生成n = 3，一直做到n = 100。
4. WHERE n < 100让查询在n = 100生成后停止，因为再往下算101时条件不满足。

别看它简单，这个“造数”能力在很多场景下是硬需求。比如说，一张流水表里不是每天都有数据，但报表要求每天一行。用递归 CTE 先把完整日期序列造出来，再 LEFT JOIN 流水表，缺的日期就能填上 0。

WITH RECURSIVE date_range AS ( SELECT '2024-01-01' AS dt UNION ALL SELECT dt + INTERVAL 1 DAY FROM date_range WHERE dt < '2024-01-31' ) SELECT dr.dt, COALESCE(SUM(s.amount), 0) AS daily_amount FROM date_range dr LEFT JOIN sales s ON dr.dt = s.sale_date GROUP BY dr.dt ORDER BY dr.dt;

这段 SQL 里，date_range生成 1 月的每一天，然后和sales表左连接，COALESCE把没发生销售的日子补成 0。这就是典型的“补全时间轴”场景，没有递归 CTE 的话，得在代码里先循环生成日期列表再来拼。

3.4 实战示例二：递归遍历部门层级

假设有一张部门表，每个部门有id和parent_id，现在要从某个部门出发，把它自己以及所有下级部门都查出来。

WITH RECURSIVE dept_tree AS ( -- 锚点：先找到根部门 SELECT id, name, parent_id, 1 AS level FROM department WHERE id = 1 UNION ALL -- 递归：找子部门 SELECT d.id, d.name, d.parent_id, dt.level + 1 FROM department d JOIN dept_tree dt ON d.parent_id = dt.id ) SELECT id, name, parent_id, level FROM dept_tree ORDER BY level, id;

这里我们在 CTE 中多算了一个level字段，表示当前是第几层。dept_tree的第一轮是部门 id=1 自己，第二轮找到 parent_id=1 的所有部门，第三轮再找这些部门的子部门，直到找不到为止。

这个写法可以做的事非常多：统计组织架构深度、查某个节点下的所有叶子节点、甚至横向展开成多列。要注意的是，如果部门表里存在环（比如 A 的父级是 B，B 的父级是 A），递归查询会死循环。虽然 MySQL 有 1001 次的迭代上限保护，但你会看到一个莫名其妙的报错而不是业务数据。所以在递归查树之前，先确认数据里没有循环引用。

3.5 递归 CTE 的限制与注意点

递归 CTE 虽好，但坑也不少。我在实际使用中整理了几个关键限制：

• 递归部分不能包含聚合函数，比如SUM()、COUNT()、GROUP BY、ORDER BY在递归部分基本都不可用。如果需要对递归结果做聚合，要么在外面包一层，要么改用其他方案。
• 递归部分的 JOIN 条件里，被递归引用的表（dept_tree）只能出现一次，并且通常是作为驱动表。
• 默认递归深度有限制，cte_max_recursion_depth系统变量的默认值是 1000。如果你确定需要更深，可以SET SESSION cte_max_recursion_depth = 10000;，但务必先想清楚：递归这么深，通常意味着业务设计有值得商榷的地方。

注意：cte_max_recursion_depth可以设置得很大，但这会让单条 SQL 占用更多内存和 CPU。线上环境如果要调大，最好先在测试库验证，别在生产库直接拉满。

4. 组合拳：WITH 搭配窗口函数、UPDATE、INSERT 的进阶用法

4.1 分组 Top N：WITH + 窗口函数

MySQL 8.0 同时引入了窗口函数和 CTE，这两者搭配起来简直是报表查询的绝配。最常见的需求是“查每个部门工资最高的前 3 名员工”。用ROW_NUMBER()+ CTE，SQL 写出来非常清爽：

WITH ranked_emp AS ( SELECT e.id, e.name, e.dept_id, e.salary, ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn FROM employee e ) SELECT r.id, r.name, d.dept_name, r.salary FROM ranked_emp r JOIN department d ON r.dept_id = d.id WHERE r.rn <= 3 ORDER BY d.dept_name, r.salary DESC;

这段 SQL 的妙处在于：窗口函数负责在每行上打一个“部门内排名”的标号，CTE 负责把它包装成一个命名的中间结果，外层只负责过滤和拼接。如果不用 CTE，这段逻辑要写成三层子查询，过滤条件rn <= 3还不能直接写在原查询里（因为窗口函数的结果不能直接用于 WHERE），必须再套一层。CTE 天然地把“计算排名”和“过滤排名”两步拆开了，这也是窗口函数 + CTE 最常见的配合姿势。

4.2 WITH 与 INSERT：把结果集直接落到表里

CTE 不只是能用在 SELECT 前面，INSERT、UPDATE、DELETE前面同样可以加。比如要把分析结果落到一张汇总表：

WITH monthly_summary AS ( SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY DATE_FORMAT(order_date, '%Y-%m') ) INSERT INTO sales_summary (stat_month, order_cnt, total_amount) SELECT month, order_cnt, total_amount FROM monthly_summary;

这样一条语句就完成了“算数”和“入库”两步操作，中间不需要任何临时表和多个语句。对于每天跑批的统计任务来说，代码能精简很多。

4.3 WITH 与 UPDATE / DELETE：先算后改更安全

再来看 DELETE 场景：删掉每个部门里工资最低的那位员工（假设这是某种极端清理需求）。这个用 CTE 加窗口函数会非常清楚：

WITH ranked_emp AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary ASC) AS rn FROM employee WHERE status = 1 ) DELETE FROM employee WHERE id IN (SELECT id FROM ranked_emp WHERE rn = 1);

写这类带 DELETE 的 CTE 时，最容易出问题的点是：MySQL 不允许直接以 CTE 作为DELETE的目标表，你必须像上面这样，通过子查询把要删的 id 取出来。如果你尝试直接DELETE FROM ranked_emp，会收到语法错误。这一点在 8.0 里也没有放开，心里要有个数。

4.4 多个 CTE 之间的互相引用进阶：像函数一样组合逻辑

前面说过第二个 CTE 可以引用第一个 CTE。这里再延伸一步，展示一种“中间结果复用同一份数据做多样分析”的姿势：

WITH base_orders AS ( SELECT customer_id, order_id, amount, order_date FROM orders WHERE order_date >= '2024-01-01' ), customer_stats AS ( SELECT customer_id, COUNT(DISTINCT order_id) AS order_cnt, SUM(amount) AS total_spent FROM base_orders GROUP BY customer_id ), high_value_customers AS ( SELECT customer_id FROM customer_stats WHERE total_spent > 5000 ) SELECT c.customer_name, cs.order_cnt, cs.total_spent, bo.order_date AS last_order_date FROM high_value_customers hvc JOIN customer c ON hvc.customer_id = c.id JOIN customer_stats cs ON hvc.customer_id = cs.customer_id LEFT JOIN base_orders bo ON hvc.customer_id = bo.customer_id AND bo.order_date = ( SELECT MAX(order_date) FROM base_orders b2 WHERE b2.customer_id = hvc.customer_id ) ORDER BY cs.total_spent DESC;

这个例子有点复杂，但本质上是把整个分析流程拆成四层：

1. base_orders：从订单表里抽出分析所需的字段，作为整个分析的底座。
2. customer_stats：在底座上做客户维度的汇总。
3. high_value_customers：过滤出高价值客户名单。
4. 主查询：把前面几层整合起来，补齐客户姓名、最后下单日期。

每层只负责一件事，你可以像搭积木一样搭出非常复杂的分析逻辑，而不用写成一座“子查询大山”。这种写法的好处是，中途任何一层出了问题，都可以单独把那段 CTE 拉出来执行验证，排查效率高得不是一点半点。

5. 性能与坑：我用 WITH 时踩过的那些雷

5.1 别以为 CTE 一定更快：物化与合并

很多初学者误以为 CTE 是“缓存中间结果”，用了就能少算几遍。但实际上，MySQL 8.0 对 CTE 的处理方式有两种：

• MERGE（合并）：优化器把 CTE 的定义直接内联到主查询中，相当于变成了子查询，不会产生额外中间存储。
• TEMPORARY TABLE（物化）：优化器把 CTE 结果实际执行一遍并存入内部临时表，主查询再扫描内部临时表。

优化器自己会根据成本选择哪种方式，并不是所有 CTE 都会被物化。所以，同一个 CTE 在主查询里被引用两次，MySQL 有可能会在内存中物化一份让它只算一次，但也有可能会内联成两份分别执行。这得看数据量、索引、内存参数等综合情况。

我的建议是：写 CTE 优先考虑可读性，如果发现查询慢，用EXPLAIN ANALYZE去看看它到底走了 MERGE 还是 TEMPTABLE，再针对性优化。比如，如果 CTE 被大量引用且计算开销高，可以在外层加个合适的索引，或者考虑改成临时表绑定索引。

5.2 递归深度和无限循环

这一条我在前面提过，但还是值得单独拿出来强调。没有终止条件的递归 CTE 会直接触发 MySQL 的迭代上限保护，报错信息是：

Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

看到这个报错，第一时间要做的不是调大cte_max_recursion_depth，而是检查递归条件是不是写错了。比如我把部门树的递归条件写成JOIN dept_tree dt ON d.parent_id = dt.parent_id，而不是d.parent_id = dt.id，那就等于每一轮都在拿兄弟节点互相 JOIN，数据行数爆炸式增长不说，还永远不会到达退出条件。这种 bug 写得开开心心，跑起来直接怀疑人生。

5.3 递归 CTE 里不能用的操作

根据 MySQL 官方文档和我的实测，递归 CTE 的递归部分有严格的限制：

• 不能用GROUP BY和聚合函数。
• 不能用窗口函数。
• 不能用DISTINCT（UNION DISTINCT除外）。
• 不能引用同一个 CTE 多次。
• 非递归部分可以用聚合，但递归部分不行。

如果你在递归部分写了：

WITH RECURSIVE cte AS ( SELECT 1 AS n UNION ALL SELECT n + COUNT(*) FROM cte GROUP BY n WHERE n < 10 ) SELECT * FROM cte;

会直接报Unsupported recursive query之类的错误。出现这种需求，说明你的递归逻辑设计有问题，建议停下来重新思考：是不是应该先递归生成数据，再在外部做聚合？

5.4 排序和分页的问题

递归 CTE 里尽量不要直接ORDER BY+LIMIT，尤其是在递归部分。因为每次递归都会尝试执行排序和分页，逻辑上很容易出现意想不到的结果。如果要对最终结果做排序分页，正确的姿势是先递归完整数据集，在外层再排序分页。

至于 WITH 主查询里的ORDER BY，没有这种限制，正常使用即可。

5.5 与临时表、视图配合时的注意事项

如果你把 CTE 写在视图里面，那也是完全没问题的，视图定义里可以用 WITH。但我遇到过一种比较隐蔽的情况：视图里用了递归 CTE，外层查询又对这个视图进行了多次 JOIN，结果性能突然变得特别差。原因是每次引用视图都会触发一次递归计算，又没有合适的物化或缓存机制。

这种情况下，我通常的做法是把视图里的递归结果改写成一张实体临时表，先物化出来再 JOIN，性能会稳很多。这也呼应了前面那张选型表：CTE 适合单条 SQL 内使用，但要跨语句复用或频繁 JOIN，还是需要临时表来兜底。

5.6 快速定位 CTE 性能问题的排查步骤

如果你遇到 CTE 语句特别慢，按下面这个顺序排查基本能覆盖 90% 的情况：

1. 用EXPLAIN ANALYZE跑一遍，看每一步的实际耗时和行数。
2. 检查 CTE 定义里的过滤条件能否用到索引。CTE 本质上还是 SELECT，索引失效的规则一样适用。
3. 检查 CTE 被引用多次时，是 MERGE 还是 TEMPTABLE。如果是Materialize，说明中间结果被实体化了，看看临时表大小是否异常。
4. 如果递归 CTE 很慢，减少一些不必要的字段，缩短每轮递归要携带的数据。
5. 最后再考虑改写成临时表，把复杂计算一次物化，加索引，再继续后续查询。

6. 更多实用技巧：ORDER BY、JOIN、执行计划等场景中的 WITH

6.1 WITH 与排序场景的结合

日常开发里，排序有两种玩法：一种是直接在ORDER BY里用字段排序，另一种是“按规则排序”，比如“按某个分组内的最新时间排序”。后者配合窗口函数和 CTE 会很顺手。

比如要拉取所有客户的最近一单信息，并按最近下单时间倒序排列：

WITH latest_orders AS ( SELECT customer_id, order_date, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) AS rn FROM orders ) SELECT c.customer_name, lo.order_date, lo.amount FROM latest_orders lo JOIN customer c ON lo.customer_id = c.id WHERE lo.rn = 1 ORDER BY lo.order_date DESC;

这里的关键点在于：窗口函数在 CTE 里计算好每个客户的订单序号，外层用rn = 1取最近一单，最后ORDER BY直接按日期排序。整个过程没有多余的嵌套，逻辑也符合人的思考顺序。

6.2 在 JOIN 中作为“宽表”使用 WITH

还有一种常见姿势，把多个 CTE 通过 JOIN 拼成一张宽表，供主查询使用。比如要分析学生成绩，需要同时带出学生基本信息、班级信息、成绩排名：

WITH student_base AS ( SELECT id, name, class_id, enroll_date FROM student WHERE status = 1 ), score_stats AS ( SELECT student_id, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM exam_record GROUP BY student_id ), class_name_map AS ( SELECT id, class_name FROM class ) SELECT sb.name, cn.class_name, ss.avg_score, ss.max_score, RANK() OVER (ORDER BY ss.avg_score DESC) AS avg_rank FROM student_base sb LEFT JOIN score_stats ss ON sb.id = ss.student_id LEFT JOIN class_name_map cn ON sb.class_id = cn.id ORDER BY ss.avg_score DESC;

这种“先定义主表，再定义补充维度，最后统一 JOIN”的模式，比把所有表一股脑全塞进一个大 JOIN 里要清晰得多。新来的人维护这段 SQL 时，只要看 WITH 部分，就知道每张表在逻辑中扮演的角色。

6.3 用 EXPLAIN 来看 CTE 的执行计划

前面反复提到执行计划，这里给出一个实际查看的例子。还是用 3.3 的部门树为例：

EXPLAIN ANALYZE WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 1 AS level FROM department WHERE id = 1 UNION ALL SELECT d.id, d.name, d.parent_id, dt.level + 1 FROM department d JOIN dept_tree dt ON d.parent_id = dt.id ) SELECT * FROM dept_tree;

EXPLAIN ANALYZE会输出每个节点实际执行时的耗时、行数、循环次数。对于递归查询，你可以看到递归部分被反复执行了多少次、每次扫描了多少行。如果递归次数特别多，且每次扫描数据的行数都很大，就要考虑是不是索引缺失，或者在department.parent_id上建索引来加速 JOIN。

在parent_id这类外键字段上建索引，是递归查询优化的第一选择。没有索引的情况下，每一轮递归都会触发全表扫描，树一旦深一点，查询时间马上指数级增长。

6.4 WITH 在不同客户端和连接方式下的表现

无论你用命令行、Navicat、MySQL Workbench 还是 Java 程序里的 JDBC 连接，WITH 语法的执行是一致的，行为不会因为客户端不同而变化。需要注意的只有一点：有些老版本的图形化工具对多行 CTE 的高亮支持不太好，看着像是语法错误，其实执行没问题。遇到这种情况，可以先在命令行里试一把。

另外，如果是通过 JDBC 执行包含 WITH 的语句，确保连接属性里不要设置奇怪的模式，这一步基本不用额外操作，正常连接即可。

7. 从实际项目出发：一段复杂 SQL 的 WITH 重构实录

7.1 背景：一段多人维护后失控的 SQL

之前接过一个数据报表优化的活，有一段 SQL 是历史遗留的“面条 SQL”。需求本质不复杂：统计每个销售在 2024 年上半年的订单数量、订单总额、退款金额，并且只保留退款率超过 20% 的销售。但代码经过多人维护，变成了大约 120 行的多层嵌套子查询，中间还有两处相同过滤条件各写各的，后来需求改动时只改了一处，结果数据错了整整两周才发现。

7.2 重构过程：从子查询到 WITH 的逐步拆解

我拿到这段 SQL 后，没有直接动手重写，而是先理清了它的逻辑，把它拆成四层：

• 订单汇总：每个销售在统计周期内的订单数和订单总额。
• 退款汇总：每个销售在统计周期内的退款金额。
• 合并明细：把上面两个结果 JOIN 起来，算出退款率。
• 过滤输出：只保留退款率大于 20% 的销售。

然后按这个分层结构，逐一写成 CTE：

WITH order_summary AS ( SELECT sales_id, COUNT(*) AS order_cnt, SUM(amount) AS order_amount FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01' AND status IN ('paid', 'completed') GROUP BY sales_id ), refund_summary AS ( SELECT sales_id, SUM(refund_amount) AS refund_amount FROM refund_records WHERE refund_date >= '2024-01-01' AND refund_date < '2024-07-01' GROUP BY sales_id ), merged AS ( SELECT os.sales_id, os.order_cnt, os.order_amount, COALESCE(rs.refund_amount, 0) AS refund_amount FROM order_summary os LEFT JOIN refund_summary rs ON os.sales_id = rs.sales_id ), final_result AS ( SELECT sales_id, order_cnt, order_amount, refund_amount, refund_amount / order_amount AS refund_rate FROM merged WHERE order_amount > 0 ) SELECT e.name, fr.order_cnt, fr.order_amount, fr.refund_amount, ROUND(fr.refund_rate * 100, 2) AS refund_rate_percent FROM final_result fr JOIN employee e ON fr.sales_id = e.id WHERE fr.refund_rate > 0.2 ORDER BY fr.refund_rate DESC;

重构完成后，这段 SQL 从 120 行降到约 60 行，更重要的是每一步逻辑都有名字，后续谁要改退款率阈值，直接改最后一层 WHERE；谁要加订单状态过滤，直接改order_summary里的条件。改起来再也不会“牵一发而动全身”还看不见是哪根线。

7.3 重构后的收益和遗留问题

重构上线之后，最直接的感受是运维同学开心了。之前数据出问题，要人肉去拆解那段多层嵌套 SQL，每次都要小心翼翼。现在每个 CTE 都可以单独跑出来定位问题，基本上 10 分钟内能定位到是哪个环节算错了。

当然，CTE 不是银弹。重构时我也发现，由于原 SQL 里有一部分字段在多层嵌套中实际没用到，重写时我把它们去掉了，结果某些依赖这字段的报表临时报错，后来花了点时间补回去。这里也提醒大家：重构 SQL 时，先确认清楚字段真的没被用到再删，别自信过头。

8. 最后的经验总结：用 WITH 的正确打开方式

回到开头那个问题：WITH 到底是语法糖还是神器？我的看法是——它本身不会让 SQL 跑得更快，但它能让写 SQL 的人、读 SQL 的人、维护 SQL 的人，都不再为逻辑混乱而痛苦。现代业务复杂度越来越高，SQL 的可读性已经不只是代码风格问题，而是数据准确性的问题。一段别人看不懂的 SQL，迟早会被改错。

我个人在实际使用中，已经养成了一个习惯：只要一段 SQL 里出现两层以上的子查询嵌套，我就优先考虑用 WITH 拆开；只要一段 SQL 要处理层级数据，我就直接想到递归 CTE；只要窗口函数要参与复杂分析，我几乎必然搭配 WITH 一起用。这套组合拳打下来，写 SQL 的质量和效率都有明显提升。

最后再分享一个小技巧：在你刚开始用 WITH 的时候，可能会觉得它比直接写子查询更像在“做工程”。别怀疑，这就是正确的方向。SQL 也是代码，代码就要讲究可读、可维护。把每一段临时结果集当做一个命名清晰的模块来设计，你会慢慢感受到这种写法的上头之处——毕竟，一个能让人一眼读懂的复杂查询，本身就是一件让人觉得舒服的事。