前段时间帮同事 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 ANALYZE8.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 与派生表、视图、临时表的选择对比这里把我实际工作中对四类方案的选型心得整理成一张表方便你对照对比维度CTE (WITH)派生表FROM 子查询临时表CREATE TEMPORARY TABLE视图CREATE VIEW可读性优秀有名字、可分层差嵌套深了很难读尚可但需要维护多段 SQL优秀封装成逻辑对象作用范围单条 SQL 语句内单条 SQL 语句内当前会话可跨多条 SQL持久存在跨会话复用性同一条语句内可多次引用不可复用可多次引用但要手动 DROP任意地方复用可递归支持WITH RECURSIVE不支持需手动循环不支持递归性能表现取决于优化器通常与派生表等价由优化器决定需要额外创建、写入、清理每次引用都要解析视图定义我的建议很简单如果中间结果集只需要在一条 SQL 里用优先用 CTE如果要跨多条 SQL 使用或者中间结果集特别大、重复计算代价高再考虑临时表视图则适合把“基础宽表”或“通用过滤规则”沉淀下来让业务层反复使用。3. 递归 CTE一行数据也能玩出树的形状3.1 什么是递归 CTE什么时候需要它递归 CTE 是 WITH 语法里最让人眼前一亮的部分。它允许一个 CTE 引用自身从而在一条 SQL 里实现循环或递归遍历。这在处理树形结构、层级关系、连续序列时非常有用。典型场景包括组织结构树从某个主管开始往下查所有直属和间接下属。商品分类树查某个分类及其所有子分类下的商品。无限级菜单一次查出一个菜单及其全部子菜单。生成长度不定的连续日期序列比如补齐报表里缺失的日期。在 MySQL 8.0 之前这类需求往往要写存储过程或者程序代码去循环。有了递归 CTESQL 自己就能搞定代码量少一大截。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 的执行过程大致是先执行锚点得到第一行n 1。递归部分基于n 1生成n 2。基于n 2生成n 3一直做到n 100。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的第一轮是部门 id1 自己第二轮找到 parent_id1 的所有部门第三轮再找这些部门的子部门直到找不到为止。这个写法可以做的事非常多统计组织架构深度、查某个节点下的所有叶子节点、甚至横向展开成多列。要注意的是如果部门表里存在环比如 A 的父级是 BB 的父级是 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 NWITH 窗口函数MySQL 8.0 同时引入了窗口函数和 CTE这两者搭配起来简直是报表查询的绝配。最常见的需求是“查每个部门工资最高的前 3 名员工”。用ROW_NUMBER() CTESQL 写出来非常清爽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;这个例子有点复杂但本质上是把整个分析流程拆成四层base_orders从订单表里抽出分析所需的字段作为整个分析的底座。customer_stats在底座上做客户维度的汇总。high_value_customers过滤出高价值客户名单。主查询把前面几层整合起来补齐客户姓名、最后下单日期。每层只负责一件事你可以像搭积木一样搭出非常复杂的分析逻辑而不用写成一座“子查询大山”。这种写法的好处是中途任何一层出了问题都可以单独把那段 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和聚合函数。不能用窗口函数。不能用DISTINCTUNION 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 BYLIMIT尤其是在递归部分。因为每次递归都会尝试执行排序和分页逻辑上很容易出现意想不到的结果。如果要对最终结果做排序分页正确的姿势是先递归完整数据集在外层再排序分页。至于 WITH 主查询里的ORDER BY没有这种限制正常使用即可。5.5 与临时表、视图配合时的注意事项如果你把 CTE 写在视图里面那也是完全没问题的视图定义里可以用 WITH。但我遇到过一种比较隐蔽的情况视图里用了递归 CTE外层查询又对这个视图进行了多次 JOIN结果性能突然变得特别差。原因是每次引用视图都会触发一次递归计算又没有合适的物化或缓存机制。这种情况下我通常的做法是把视图里的递归结果改写成一张实体临时表先物化出来再 JOIN性能会稳很多。这也呼应了前面那张选型表CTE 适合单条 SQL 内使用但要跨语句复用或频繁 JOIN还是需要临时表来兜底。5.6 快速定位 CTE 性能问题的排查步骤如果你遇到 CTE 语句特别慢按下面这个顺序排查基本能覆盖 90% 的情况用EXPLAIN ANALYZE跑一遍看每一步的实际耗时和行数。检查 CTE 定义里的过滤条件能否用到索引。CTE 本质上还是 SELECT索引失效的规则一样适用。检查 CTE 被引用多次时是 MERGE 还是 TEMPTABLE。如果是Materialize说明中间结果被实体化了看看临时表大小是否异常。如果递归 CTE 很慢减少一些不必要的字段缩短每轮递归要携带的数据。最后再考虑改写成临时表把复杂计算一次物化加索引再继续后续查询。6. 更多实用技巧ORDER BY、JOIN、执行计划等场景中的 WITH6.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% 的销售。然后按这个分层结构逐一写成 CTEWITH 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 也是代码代码就要讲究可读、可维护。把每一段临时结果集当做一个命名清晰的模块来设计你会慢慢感受到这种写法的上头之处——毕竟一个能让人一眼读懂的复杂查询本身就是一件让人觉得舒服的事。