最近在做一批历史报表SQL的梳理遇到一个印象很深的查询统计各区域销售Top 10产品源数据就三张表写出来的SQL却有将近三百行光子查询嵌套就有七层。不是说跑不动而是后来同事想在这个基础上加一个过滤条件对着那段SQL看了一整个下午愣是没敢动手。我后来用MySQL 8.0的WITH语法也就是公共表表达式CTE重构了一遍核心逻辑压到七十行左右每个模块都能独立看懂。从那以后我们团队就定了个规矩连续三层以上的子查询必须考虑改用CTE。这篇文章想把MySQL里WITH的几种常见用法和示例一次性讲清楚覆盖基础写法、递归查询、多重编排、窗口函数混搭以及我在实际优化中踩过的一些坑。刚接触CTE的SQL开发可以用它当入门手册已经在老系统里维护复杂查询的DBA也可以参考后面的性能与兼容性部分。1. 先搞明白CTE到底解决了什么问题1.1 三层嵌套子查询的噩梦在没有WITH之前复杂报表大概长这样SELECT city, SUM(last_amount) AS city_total FROM ( SELECT u.city, t.last_amount FROM user u JOIN ( SELECT user_id, amount AS last_amount FROM orders o WHERE o.order_date ( SELECT MAX(order_date) FROM orders o2 WHERE o2.user_id o.user_id ) ) t ON u.user_id t.user_id ) tmp GROUP BY city;这段SQL的逻辑其实不复杂先找出每个用户的最近一笔订单金额再按城市汇总。但阅读顺序是从里往外执行顺序却又是另一回事。改一个条件要一层一层翻查重的时候还得反复盯括号。更麻烦的是如果同一段“用户最近订单”逻辑在另一个查询里也要用只能整段复制改一处就要同步改好几处。这个痛点不是性能问题而是可维护性问题。SQL本身是声明式语言写的时候脑子里想的是“步骤”但子查询要求你把“步骤”倒着写。层数越多人的脑力就越跟不上。1.2 CTE的本质一条SQL里的“命名临时结果集”CTE的写法很直白WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name;它可以理解为在同一条SQL的执行过程中给一段查询起个名字后面可以直接引用这个名字。它不是物理表也不会长期存在当这条SQL执行结束CTE的生命周期就结束了。它的价值在于把“倒着写”变回“顺着写”。你先定义第一步是什么、第二步是什么最后一步主查询直接使用前面定义好的结果。阅读SQL就像在读一份从上到下的操作清单而不是在一堆括号里做迷宫题。1.3 CTE与派生表的区别不只是换个写法很多人觉得CTE就是把派生表往前一提实际上差别不止表面。我整理了一个对比表对比维度派生表FROM子查询CTEWITH阅读顺序从最内层往外读从上往下先定义后使用同一查询块复用很难只能重复写可以定义一次引用多次递归支持不支持支持WITH RECURSIVE可读性嵌套越深越差线性展开相对友好执行策略可能被优化器合并或物化也可能物化或合并策略不同派生表在FROM里出现每个子查询都是一个相对独立的作用域想在外部再引用它只能重新写一遍。CTE则给了这段查询一个名字后续多个JOIN、多次计算都可以复用同一个名字。当然CTE不是银弹。后面会专门讲性能但在可读性这个维度上CTE对复杂SQL的改善是实打实的。2. WITH的基础语法与普通CTE的典型写法2.1 单CTE示例报表中最简单的场景假设要统计2024年每个客户的累计消费额并且只看消费超过1万元的客户。WITH customer_sales AS ( SELECT customer_id, SUM(order_amount) AS total_amount FROM orders WHERE order_date BETWEEN 2024-01-01 AND 2024-12-31 GROUP BY customer_id ) SELECT c.customer_name, cs.total_amount FROM customers c JOIN customer_sales cs ON c.customer_id cs.customer_id WHERE cs.total_amount 10000 ORDER BY cs.total_amount DESC;这段SQL的逻辑非常清晰WITH里做聚合主查询做关联和过滤。如果不用CTE这段聚合逻辑就得写成一个子查询放在JOIN的位置代码看上去也没问题但一旦聚合逻辑变复杂比如要同时统计累计消费、累计订单数、最近一次消费时间套在FROM里的子查询就会迅速变得臃肿。CTE可以同时包含多个列比如WITH customer_stats AS ( SELECT customer_id, COUNT(*) AS order_count, SUM(order_amount) AS total_amount, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id ) SELECT ...这就是普通CTE最常见的用途把聚合结果预计算好主查询负责做展示层逻辑。2.2 多CTE并列一个WITH后面挂多段查询当一个报表需要多个中间结果时可以在一个WITH后面用逗号并列定义多个CTEWITH active_users AS ( SELECT user_id FROM user_login_log WHERE login_date 2024-06-01 GROUP BY user_id ), high_value_orders AS ( SELECT user_id, SUM(order_amount) AS total_amount FROM orders WHERE order_date 2024-06-01 GROUP BY user_id HAVING SUM(order_amount) 5000 ) SELECT a.user_id, h.total_amount FROM active_users a JOIN high_value_orders h ON a.user_id h.user_id;注意多个CTE之间用逗号分隔最后一个CTE后面直接跟主查询。这种写法非常适合“多条件筛选后的交集、并集、差集”场景每一类用户先分别定义最后再组合。多CTE之间也可以存在依赖关系后面的CTE引用前面CTE的结果这个特性在第四章会重点展开。2.3 在INSERT、UPDATE、DELETE中使用CTEWITH不仅可以配合SELECTMySQL 8.0也允许CTE出现在INSERT、UPDATE、DELETE语句前。这在实际工作中非常实用。将每日明细聚合成月汇总表INSERT INTO monthly_sales_summary (month, store_id, total_amount) WITH daily_sales AS ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month, store_id, order_amount FROM orders WHERE order_date 2024-01-01 ) SELECT month, store_id, SUM(order_amount) AS total_amount FROM daily_sales GROUP BY month, store_id;批量更新场景也能用UPDATE customers c JOIN ( SELECT customer_id, SUM(order_amount) AS total_amount FROM orders WHERE order_status paid GROUP BY customer_id ) t ON c.customer_id t.customer_id SET c.total_paid t.total_amount;这里我故意用子查询做对照意思是想说明有些DML场景直接写子查询就够了不一定要换成CTE。CTE的优势主要体现在“这个中间结果下面还要用两三次”的时候如果只用一次两种写法性能差异往往不大选自己熟悉且不容易写错的即可。2.4 基础语法的几条硬性约束用CTE时容易踩到几个语法边界列出来免得大家反复试错CTE名称在使用它的同一条SQL里必须唯一不能重复定义同名CTE。主查询必须紧跟在最后一个CTE定义之后不能中途插入其他语句。后面的CTE只能引用前面已经定义好的CTE不能反向引用后面还没定义的CTE。在CTE内部可以使用AS显式指定列名例如WITH t(col1, col2) AS (SELECT ...)。CTE定义里的SELECT一般不需要ORDER BY。除非配合LIMIT否则排序属于无意义操作因为外部查询可能会重新排序。其中最后一条很多人会忽略。CTE不是视图不保证顺序语义你在这个CTE里ORDER BY外面一JOIN顺序可能就变了。3. 递归CTE树形结构数据的最佳搭档3.1 递归CTE语法锚点成员加递归成员递归CTE是WITH语法里最亮眼的部分它可以处理“层级关系”和“序列生成”这两类问题。语法结构如下WITH RECURSIVE cte_name AS ( SELECT ... -- 锚点成员初始数据 UNION ALL SELECT ... -- 递归成员引用CTE自身 FROM cte_name WHERE ... ) SELECT * FROM cte_name;锚点成员负责给递归一个起点递归成员负责不断向上一步的结果追加数据。必须使用UNION ALL而不是UNION使用UNION会去重很可能打乱递归逻辑并带来额外性能开销。3.2 数字序列与日期序列一行递归搞定不用造一张数字表直接生成1到100的序列WITH RECURSIVE number_seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM number_seq WHERE n 100 ) SELECT n FROM number_seq;执行过程可以理解为先用锚点生成一行n1递归成员取出这一行算出一行n2再取出n2算出一行n3一直到n100WHERE n 100不满足才停止。把数字换成日期序列也一样这是做连续日期补全时常用的套路。比如某个业务按天记录某几天没有数据需要补零生成完整日历WITH RECURSIVE date_seq AS ( SELECT DATE(2024-01-01) AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_seq WHERE dt 2024-01-31 ) SELECT ds.dt, COALESCE(SUM(o.order_amount), 0) AS daily_amount FROM date_seq ds LEFT JOIN orders o ON DATE(o.order_date) ds.dt GROUP BY ds.dt;这个查询能从日期维度左连接订单表没有订单的日期也会输出0而不是像普通GROUP BY那样直接缺失日期行。3.3 组织架构树查询某部门及所有子部门假设有部门表CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(64) NOT NULL, parent_id INT NULL );这里用一个模拟电商公司的组织树做例子。现在要查询某个部门及其所有下级部门普通写法需要自连接很多次层级不固定时根本写不了。递归CTE可以这样WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 0 AS depth FROM departments WHERE id 1001 UNION ALL SELECT d.id, d.name, d.parent_id, dt.depth 1 FROM departments d JOIN dept_tree dt ON d.parent_id dt.id ) SELECT id, name, parent_id, depth FROM dept_tree;锚点先取出目标部门递归成员每次找到父部门等于当前结果集里某个部门的下一级子部门同时深度加1。最终结果会包含所有层级的子部门。这类逻辑在权限范围查询、商品类目树、区域管辖范围、BOM物料清单里都非常常见。3.4 反向遍历从子节点向上找完整链路有时需要反向找全链路比如从某个具体员工出发一路向上找他的汇报链WITH RECURSIVE report_chain AS ( SELECT emp_id, emp_name, manager_id, 0 AS depth FROM employees WHERE emp_id 9002 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, rc.depth 1 FROM employees e JOIN report_chain rc ON e.emp_id rc.manager_id ) SELECT emp_id, emp_name, manager_id, depth FROM report_chain;注意JOIN条件反过来了上一轮的结果在manager_id这一侧下一轮通过manager_id去employees表里找对应员工。锚点换成任意子节点这条SQL就能向上递归到根节点。这类“血缘追溯”“审批链路”场景很常用。3.5 递归深度与防死循环必须掌握的边界递归CTE有一个系统参数cte_max_recursion_depth默认值是1000。也就是说递归超过1000次时MySQL会直接报错Recursive query aborted after 1001 iterations.我在实际业务里遇到过部门层级数据不干净的情况某条记录把自己的父部门指向了自己或者两个部门互相指来指去。这种数据一旦跑递归CTE就会变成死循环直到撞上1000层上限才停下既浪费时间又刷日志。处理办法有两个思路在递归成员里加深度限制比如WHERE rc.depth 20从业务角度兜底。先在应用层或SQL前置逻辑里清洗数据确保没有环引用。加深度限制是最快的防护手段即使数据有环也不会无限跑下去。递归成员里还有一个限制不允许使用聚合函数、窗口函数等可能破坏递归语义的操作。先递归出完整结果集再在外面包一层做聚合才是正确姿势。4. 多重CTE编排把复杂SQL拆成阶梯4.1 后面的CTE可以引用前面的CTE这个特性非常实用。前面的CTE定义好以后后面的CTE可以直接把它当表用一层一层往下推WITH base AS ( SELECT customer_id, order_id, order_amount, order_date FROM orders WHERE order_status paid ), daily_base AS ( SELECT customer_id, order_date, SUM(order_amount) AS daily_amount FROM base GROUP BY customer_id, order_date ), customer_rank AS ( SELECT customer_id, order_date, daily_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY daily_amount DESC) AS rn FROM daily_base ) SELECT * FROM customer_rank WHERE rn 3;整个查询像搭台阶一样每一级只做一件事。第一级过滤订单第二级做日聚合第三级算每个客户每日消费额的排名最后主查询取每个客户消费最高的三天。阅读顺序和执行计划虽然不完全一致但人的理解顺序是线性的排查问题会轻松很多。4.2 用户连续登录天数一步步拆解连续登录天数是一个非常经典的SQL面试题用CTE拆解后再看每一步都不难。第一步先去重一个用户一天可能登录多次WITH login_dedup AS ( SELECT user_id, login_date FROM user_login_log WHERE login_date BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY user_id, login_date )第二步用ROW_NUMBER给每个用户每天的登录日期编号然后计算login_date减去编号得到的日期差login_with_rank AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM login_dedup ), login_with_grp AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp FROM login_with_rank )这里的关键点是连续登录的日子里login_date - rn这个值是相等的。比如某用户1号、2号、3号连续登录rn分别是1、2、3算出来的grp都是0号日期如果中间断了一天后面的日期减掉新的编号就会产生不同的grp于是连续登录区间就被切分开了。第三步按用户和grp分组算出每段的连续天数login_streak AS ( SELECT user_id, grp, COUNT(*) AS streak_days, MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM login_with_grp GROUP BY user_id, grp ) SELECT user_id, MAX(streak_days) AS max_continuous_days FROM login_streak GROUP BY user_id;整个四五层CTE串在一起每层都是上一层的简单加工。如果不用CTE把所有逻辑塞进一个多层子查询里光是维护括号就够受的了。4.3 什么时候仍然该用子查询而不是CTECTE虽好也不是所有场景都该用。我的判断标准是如果中间结果只使用一次、逻辑又很短直接写子查询或JOIN子查询更简单。如果中间结果需要多次引用或者逻辑层级超过三层优先CTE。如果主要是为了“给一段查询命名”可以考虑到不了CTE的程度直接用视图可能更合适毕竟视图可以长期复用。还有一个容易忽略的点CTE命名的中间结果如果特别大并且只被引用一次MySQL物化结果和直接内联子查询的执行路径不一定一样性能差异会在第六章详细说。5. CTE与窗口函数、聚合混搭报表统计的黄金组合5.1 为什么窗口函数离不开外层包裹SQL里不允许在WHERE子句直接使用窗口函数的别名比如SELECT user_id, order_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS rn FROM orders WHERE rn 5; -- 错误上面的写法会报错因为WHERE执行顺序在窗口函数计算之前。通常解决办法是包一层子查询。用CTE包这一层语义会清晰很多WITH ranked_orders AS ( SELECT user_id, order_id, order_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS rn FROM orders WHERE order_status paid ) SELECT user_id, order_id, order_amount FROM ranked_orders WHERE rn 5;这就是CTE和窗口函数最常见的配合玩法先算排名再过滤排名。5.2 累计求和与累计占比做帕累托分析统计每个产品的销售额后再算累计占比也适合用CTE分层处理WITH product_sales AS ( SELECT product_id, SUM(order_amount) AS sales_amount FROM orders WHERE order_date 2024-01-01 GROUP BY product_id ), sales_calc AS ( SELECT product_id, sales_amount, SUM(sales_amount) OVER () AS total_sales, SUM(sales_amount) OVER (ORDER BY sales_amount DESC) AS running_sales FROM product_sales ) SELECT product_id, sales_amount, ROUND(sales_amount / total_sales * 100, 2) AS sales_percent, ROUND(running_sales / total_sales * 100, 2) AS cumulative_percent FROM sales_calc ORDER BY sales_amount DESC;先聚合出每个产品销售额再用SUM() OVER ()算全量总销售额同时用ORDER BY sales_amount DESC的累计窗口算运行总计。CTE把“聚合”和“窗口计算”拆成两层比一个子查询从头写到尾舒服很多。5.3 同环比CTE天然适合先各算各的最后再JOIN月度同环比报表我习惯先分别定义“本期”和“上期”两个CTE最后再关联对比WITH current_month AS ( SELECT store_id, SUM(order_amount) AS amount FROM orders WHERE DATE_FORMAT(order_date, %Y-%m) 2024-06 GROUP BY store_id ), previous_month AS ( SELECT store_id, SUM(order_amount) AS amount FROM orders WHERE DATE_FORMAT(order_date, %Y-%m) 2024-05 GROUP BY store_id ) SELECT cur.store_id, cur.amount AS current_amount, prev.amount AS previous_amount, ROUND((cur.amount - prev.amount) / prev.amount * 100, 2) AS mom_growth FROM current_month cur LEFT JOIN previous_month prev ON cur.store_id prev.store_id;各算各的最后再做计算列。如果不拆分就得在同一个聚合里用CASE WHEN把月份条件写进去逻辑一旦多了很容易乱。5.4 一个报表里同时出现多次同一个CTECTE最爽的一点是可以被主查询引用多次。比如一个CTE算出所有员工销售额主查询里既想查看总排名又要看与最高值对比WITH emp_sales AS ( SELECT emp_id, SUM(sales_amount) AS total_sales FROM sales_records WHERE sale_date 2024-01-01 GROUP BY emp_id ) SELECT e.emp_id, e.total_sales, (SELECT MAX(total_sales) FROM emp_sales) AS max_sales, ROUND(e.total_sales / (SELECT MAX(total_sales) FROM emp_sales) * 100, 2) AS pct_of_max FROM emp_sales e;这里emp_sales被引用了三次。如果不用CTE光那个子查询就要复制三次改动一个聚合条件三处都要跟着改。CTE只需要改一个地方。6. 性能实测与避坑记录CTE不是银弹6.1 MySQL 8.0的物化机制它不一定比子查询快很多人在要不要用CTE上纠结核心就是怕性能变差。MySQL 8.0对CTE的执行策略有两种直接合并到外层查询或者物化成临时表。优化器会自己选我做过的几个测试结果是小结果集且CTE只被引用一次优化器通常选择直接合并这和普通子查询差异不大大结果集或CTE被引用多次优化器可能选择物化物化结果存在临时表里可以复用但物化本身有开销物化出来的临时表如果没有合适索引后续JOIN可能出现扫描开销尤其数据量大时容易被放大。所以在把所有子查询改成CTE之前最好先针对关键SQL做EXPLAIN ANALYZE对比。我曾经把一个七层子查询报表改成六层CTE执行计划反而多了两次物化整体耗时从1.8秒变成2.6秒。原因就是CTE结构太宽优化器选择了物化策略而物化后的临时表和另一个表JOIN时没有索引走了全表扫描。后来我把CTE拆细了一些并且把过滤能力尽量下沉到锚点CTE里物化的临时表变小了耗时才降下来。6.2 CTE与临时表、派生表的取舍可以把三者的特点放在一起看方案优点缺点典型适用场景普通子查询简单直接不生成临时表可读性差多次重复逻辑只能复制简单过滤、单层聚合CTE复用性强、可递归、可读性好可能物化调优依赖优化器策略复杂报表、树形查询、多次引用显式临时表可自主添加索引需要额外的CREATE/DROP语句超大中间结果且SQL无法容纳CTE物化后没有索引这件事我认为是最需要留意的。如果一个CTE产出的是几十万行的中间结果并且下一步要做关联那CTE未必比一个手动创建的临时表更合适。只是在“一条SQL内解决问题”这个前提下CTE的代价仍然可控。6.3 递归CTE的常见坑递归CTE最常见的问题是死循环。前面提到过通过深度限制兜底。另外几个坑也很典型缺少UNION ALL条件限制递归成员没有WHERE终止条件直接撞上cte_max_recursion_depth上限报错。递归成员里使用聚合函数或窗口函数MySQL会报错意思是这些操作不允许出现在递归成员中。在递归结果上直接ORDER BY如果递归层数多通常应该先递归完外层再排序。UNION而不是UNION ALL会对结果去重带来额外开销还可能影响递归进度。6.4 版本兼容与迁移老库怎么用CTEMySQL 5.7及更早版本完全不支持WITH RECURSIVE普通WITH也不支持。如果线上还是MySQL 5.7又想用CTE有几个过渡思路在MariaDB 10.2以上可以部分兼容CTE但MySQL和MariaDB的递归细节不完全一致不能盲目替换。可以把复杂查询先封装成视图视图内部仍然用子查询业务侧SQL保持不变等数据库升级后再改写成CTE。如果DBA可以控制版本迁移建议明确测试递归深度、物化策略、DML中的CTE支持等差异再整体切换。实际工作中我见过不少系统因为“某个报表实在太难维护”而下决心升级到MySQL 8.0CTE正是其中很重要的推动力。6.5 我在几个真实场景里的最终建议简单总结一下我现在写复杂SQL的习惯先把业务需求拆成步骤每个步骤对应一个CTE不强求一步到位。让每个CTE只做一件事过滤、聚合、关联、排名混在一起反而更难调优。中间结果尽量在源头窄化先过滤掉不必要的数据再进入下一层。CTE被引用多次时优先用CTE引用一次且逻辑简单时优先保持简单子查询。如果卡在性能上用EXPLAIN ANALYZE看有没有物化和全表扫描再考虑把大结果集拆开。CTE最重要的价值是让SQL从“只有写的人才能看懂的谜题”变成“过两周再看也能轻松维护的清单式代码”。围绕它的性能争议会一直存在但在我维护过的项目里可读性带来的团队效率提升通常远远大于那几毫秒的执行时间差异。真正卡住瓶颈的从来不是SQL语法好不好看而是某段逻辑别人根本不敢去改动。