写 SQL 写到想摔键盘十有八九是栽在子查询嵌套上。我说的不是 WHERE 里面简单加个 IN而是 FROM 里套一层、外面再套一层三层起步那种意大利面式写法。前阵子接一个报表需求逻辑其实不算复杂先按部门算平均工资筛掉低于公司平均线的部门再跟上季度做环比。用嵌套子查询写出来别说同事我自己过两天再看都不知道哪段是干嘛的。最后全改用 WITH AS 重写四五层嵌套变成自上而下念的三段问题瞬间清爽。所以这篇文章想把 MySQL 的 WITH AS官方叫 Common Table ExpressionCTE从语法到实战、从性能到避坑一次讲透。适合刚接触 MySQL 8.0 的新手也适合想在报表和复杂查询里少掉头发的老手。1. WITH AS 到底解决什么问题从一个嵌套子查询现场说起1.1 没有 CTE 之前复杂查询是怎么写的MySQL 在 8.0 之前想在一条查询里把先算中间结果、再用中间结果继续算这种逻辑表达出来路子很窄。常见的做法是把子查询塞进 FROM 子句官方管这个叫派生表Derived Table。举个例子我们要找出平均工资最高的 10 个部门需要先按部门聚合再和部门表关联。没有 CTE 的时候大概长这样SELECT d.dept_name, s.avg_salary FROM ( SELECT dept_no, AVG(salary) AS avg_salary FROM employees GROUP BY dept_no ORDER BY avg_salary DESC LIMIT 10 ) s JOIN departments d ON s.dept_no d.dept_no;这还算好的。如果中间结果不止一层比如要先算部门平均工资再算部门平均工资的部门平均再做排名SQL 就会变成一层套一层的套娃。更麻烦的是如果同一个中间结果在主查询里要被引用两次你就得把这段子查询原样写两遍改一处忘了另一处结果对不上是常有的事。我踩过最痛的一次坑是从一个三层嵌套的查询里改一个字段名。当时改完外层内层没同步改线上报表的环比数据直接算错排查了整整一下午。那之后就明白了复杂查询最怕的不是性能是读不懂、改不动、容易错。1.2 说人话WITH AS 到底是什么WITH AS 的官方名字是 Common Table Expression中文一般翻译成公共表表达式简称 CTE。它的本质是在当前这一条 SQL 语句里先声明一个或多个有名字的临时结果集然后主查询可以像查普通表一样反复引用这些结果集。可以把它理解成查询里的草稿纸。平时做数学题你不会每一步都往卷子上写一长串算式而是先在草稿纸上算出一个中间结果起个代号后面直接拿来用。CTE 就是给 SQL 用的这张草稿纸。声明一次命名它然后在同一条语句里随便用用完这条语句结束自动释放不落库、不占表空间。这里要纠正一个常见的叫法习惯很多人说WITH AS 语法其实完整的写法是WITH 名字 AS (SELECT ...)AS 前面必须有 CTE 的名字WITH 后面跟的是声明列表不是 AS 本身。明白这一点看官方文档就不会懵。1.3 版本支持范围别在 5.7 上浪费时间CTE 是 MySQL 8.0 开始正式支持的更精确地说8.0 系列从实验性到稳定都在逐步完善。MySQL 5.7 及更早版本完全不认识 WITH 关键字你写WITH ... AS (...)直接给你报语法错误。MariaDB 从 10.2 开始也支持 CTE语法和 MySQL 大体兼容但细节上有差异后面讲递归的时候我会单独点一下。如果你还在维护 5.7 的老项目看到WITH报 1064 语法错误第一反应先查版本别急着改 SQL。行业里大量MySQL 5.7 不支持 WITH AS的提问本质上就是版本问题。升级到 8.0 之后CTE 配合窗口函数8.0 同期引入基本就是新时代 MySQL 写复杂查询的两把钥匙。对比维度WITH AS (CTE)派生表FROM 子查询临时表CREATE TEMPORARY TABLE作用域单条语句内单条语句内整个会话内可见是否需要建表否否是同一结果复用次数同语句内不限次数每次引用都要重写一遍可跨多条查询反复用自动清理语句结束自动释放语句结束自动释放会话结束或手动 DROP对权限的要求普通查询权限即可普通查询权限即可需要 TEMPORARY TABLES 权限递归支持支持用 WITH RECURSIVE不支持不支持要自己写循环可读性从上往下读最友好嵌套深了很难读逻辑分散不够直观2. 语法拆解看得懂也要写得对2.1 最基础的写法结构WITH AS 的语法骨架是固定的就这么个形状WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name;拆开看几个要点WITH 关键字必须放在整条语句的最前面前面不能再有其他子句。括号里就是一段普通的 SELECT这段 SELECT 的查询结果会成为 cte_name 这个虚拟表的内容。主查询WITH 后面紧跟的那个 SELECT / UPDATE / DELETE必须存在CTE 不是独立执行的语句它只是给主查询提供数据源。每个 CTE 之间用英文逗号分隔最后一个 CTE 后面没有逗号直接跟主查询。一个最土但最常见的例子先算部门平均工资再排序WITH dept_avg AS ( SELECT dept_no, AVG(salary) AS avg_salary FROM employees GROUP BY dept_no ) SELECT dept_no, avg_salary FROM dept_avg ORDER BY avg_salary DESC;这个例子看着多余——本来就是一条 GROUP BY 就能解决的事。但它的意义在于让你在没有其他干扰的情况下看清语法结构。实际业务里CTE 里的查询往往几百行主查询再引用它的时候SQL 的阅读顺序就和人的思考顺序完全一致了先算什么再算什么最后出什么。2.2 多个 CTE并联声明与接力引用一条语句里可以声明多个 CTE彼此用逗号隔开。这里有个容易被忽视的规则CTE 之间可以互相引用但只能引用在它前面已经声明好的CTE。也就是说CTE 的声明顺序就是它们的依赖顺序你没法在一个 CTE 里引用后面才定义的 CTE。WITH sales_summary AS ( SELECT product_id, SUM(amount) AS total_amount FROM order_items GROUP BY product_id ), product_info AS ( SELECT id, name, category FROM products ) SELECT p.name, s.total_amount FROM sales_summary s JOIN product_info p ON s.product_id p.id;这个例子里 sales_summary 是第一步聚合product_info 是第二步取商品维度信息主查询做关联。整个 SQL 读下来像流水账一样清楚。实际写的时候我习惯把数据准备步骤放在上面最终计算放在主查询里这样别人接手时不需要逆向推理你的嵌套逻辑。还有一点多个 CTE 是可以接力的后面的 CTE 可以直接查前面 CTE 的结果。这种写法特别适合把一个复杂的取数逻辑拆成几个中间层每一层只做一件事。比如先洗数据再聚合再打标签三步三个 CTE互相之间通过名字引用比嵌套子查询好维护一个数量级。2.3 给 CTE 指定列名的写法CTE 还支持在声明时显式指定列名语法是WITH dept_avg (dept_code, avg_sal) AS ( SELECT dept_no, AVG(salary) FROM employees GROUP BY dept_no ) SELECT dept_code, avg_sal FROM dept_avg;注意这里有个硬性规则CTE 名字后面括号里的列名数量必须和 AS 内 SELECT 返回的列数量完全一致多一个少一个都直接报错。调这个列名的好处有两个一是给内部复杂的计算列起个简短的外部名字二是当你不想让外部看到内部表达式时可以在这里重新命名。不过我个人用得不多。原因很简单CTE 内部的 SELECT 原本就可以写别名与其在外面再映射一层不如在内部直接把别名写好。这个功能更适合那些内部 SELECT 来自别的封装、不好改别名的情况。2.4 WITH 能用在哪些语句里很多人以为 WITH 只能配 SELECT其实 MySQL 8.0 里 CTE 的适用范围比想象中广UPDATE 和 DELETE 前面也能跟 WITH。这个特性在做批量数据修复时非常实用。举个例子给预算超过 100 万的部门的所有员工发额外奖金通常的做法是先查出一个部门清单再 UPDATE。用 CTE 可以在一条语句里完成WITH high_budget_depts AS ( SELECT dept_no FROM departments WHERE annual_budget 1000000 ) UPDATE employees e JOIN high_budget_depts h ON e.dept_no h.dept_no SET e.bonus e.bonus 500;同样DELETE 前面也可以带 WITH。我的经验是但凡遇到先算出一个范围再按这个范围做增删改的场景都值得用 CTE DML 的组合。它最大的价值是把计算范围和执行操作分开避免在 UPDATE 的 SET 或 WHERE 里塞一堆半懂不懂的子查询改起来也安全。3. 实操案例从报表统计到递归树五个必练场景3.1 场景一把一条 GROUP BY 拆成两步很多同学写报表会遇到一个经典需求既要看每个月的销售额又要看每个月的销售额占全年比例。如果一行 SQL 硬怼要么写窗口函数要么写两遍聚合。用 CTE 可以把算月销售和算占比拆开WITH monthly_sales AS ( SELECT DATE_FORMAT(order_date, %Y-%m) AS ym, SUM(amount) AS revenue FROM orders WHERE order_date 2025-01-01 AND order_date 2026-01-01 GROUP BY DATE_FORMAT(order_date, %Y-%m) ) SELECT ym, revenue, ROUND(revenue / (SELECT SUM(revenue) FROM monthly_sales) * 100, 2) AS pct FROM monthly_sales ORDER BY ym;注意看主查询里的(SELECT SUM(revenue) FROM monthly_sales)这个子查询引用的是 CTE而不是原表。这就把每月结果和全年总计都建立在同一个中间结果上不会因为聚合口径不一致产生偏差。实际跑报表时我最怕的就是月销售额是对的但占比怎么算都对不上八成就是两处聚合口径不一样。用 CTE 统一中间结果能从根上消除这种问题。3.2 场景二同一份中间结果引用多次CTE 一个很香的能力就是在一句 SQL 里引用多次而派生表做不到——你写两次 FROM 子查询就等于执行两遍SQL 文本也巨长。看这个例子要算每个品类的销售额占比WITH cat_sales AS ( SELECT c.category_name, SUM(oi.amount) AS total_sales FROM categories c JOIN products p ON c.id p.category_id JOIN order_items oi ON p.id oi.product_id GROUP BY c.category_name ) SELECT category_name, total_sales, ROUND(total_sales / (SELECT SUM(total_sales) FROM cat_sales) * 100, 2) AS share_pct FROM cat_sales ORDER BY total_sales DESC;cat_sales 这个 CTE 在主查询里被引用了两次一次是普通的 FROM 数据源一次是算占比的子查询。如果不用 CTE你得把那一长串 JOIN GROUP BY 写两遍改一个字段名就要改两处漏一处数据就差一截。CTE 把同一份结果的多处引用变成了一处定义、多处使用这是它作为工程化工具最大的价值。3.3 场景三递归 CTE 处理组织架构树递归是 CTE 的重头戏语法上要加 RECURSIVE 关键字。处理员工-主管这种树形结构是递归 CTE 最常见的应用。假设 employees 表有 emp_no、emp_name、manager_no 三个字段manager_no 为 NULL 表示顶层领导。要查出整棵组织树并带上层级深度可以这样写WITH RECURSIVE emp_tree AS ( -- 锚点成员顶层节点 SELECT emp_no, emp_name, manager_no, 1 AS depth FROM employees WHERE manager_no IS NULL UNION ALL -- 递归成员从上一层往下找下属 SELECT e.emp_no, e.emp_name, e.manager_no, t.depth 1 FROM employees e JOIN emp_tree t ON e.manager_no t.emp_no ) SELECT emp_no, emp_name, depth FROM emp_tree ORDER BY depth;递归 CTE 的写法分两部分中间用 UNION ALL 连接。上半部分是锚点anchor是递归的起点负责选出第一层数据下半部分是递归成员它引用自己每次把上一轮的结果作为输入继续往下查。MySQL 会反复执行递归部分直到某一次查询结果为空为止。这里要提醒一个关键点递归部分一般用 UNION ALL别画蛇添足写 UNION DISTINCT。递归是靠不断产生新行推进的DISTINCT 反而可能干扰迭代逻辑而且 MySQL 对递归语法有严格的限制后面避坑章节会详细说。3.4 场景四用递归生成连续日期解决报表缺日问题很多报表系统有个老大难问题某天没有销售记录查询结果里这一天就凭空消失了前端画折线图时出现断点。常规解法是维护一张日期维度表但很多时候你没有这张表。用递归 CTE 可以现场生成连续日期序列WITH RECURSIVE date_seq AS ( SELECT 2025-01-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_seq WHERE d 2025-01-31 ) SELECT d FROM date_seq;有了日期序列再和销售表做 LEFT JOIN缺失的日期自然会补出来销售额为空的置为 0WITH RECURSIVE date_seq AS ( SELECT 2025-01-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_seq WHERE d 2025-01-31 ), daily_sales AS ( SELECT order_date, SUM(amount) AS total FROM orders WHERE order_date BETWEEN 2025-01-01 AND 2025-01-31 GROUP BY order_date ) SELECT ds.d, COALESCE(s.total, 0) AS total FROM date_seq ds LEFT JOIN daily_sales s ON ds.d s.order_date ORDER BY ds.d;这个写法我几乎每个项目都用过。注意递归终止条件必须写在递归成员的 WHERE 里否则日期生成不会停。另外日期跨度如果很大比如生成三年数据要记得调递归深度上限这在避坑章节会讲。3.5 场景五和窗口函数搭档做累计值与排名MySQL 8.0 同时带来了 CTE 和窗口函数两个配合起来复杂分析查询的体验直接起飞。比如算累计销售额WITH monthly_sales AS ( SELECT DATE_FORMAT(order_date, %Y-%m) AS ym, SUM(amount) AS revenue FROM orders GROUP BY DATE_FORMAT(order_date, %Y-%m) ) SELECT ym, revenue, SUM(revenue) OVER (ORDER BY ym) AS cumulative_revenue FROM monthly_sales ORDER BY ym;这里 CTE 负责把月度聚合算好窗口函数负责在聚合结果上做累计职责非常清晰。如果不用 CTE窗口函数就得直接套在内层的 GROUP BY 结果上SQL 文本会变得很长而且 ORDER BY 的顺序稍微一乱累计逻辑就跟着乱。我的习惯是任何窗口函数的输入尽量先用 CTE 准备好这样窗口函数那一层只做逐行计算不做数据准备出问题好定位。4. 性能真相CTE 到底快不快别被表面写法骗了4.1 先搞懂物化Materialization与合并Merge聊性能之前先破除一个神话CTE 不是性能优化工具它首先是可读性工具。同样的逻辑CTE 和嵌套子查询的执行计划可能完全一样也可能差别很大具体取决于 MySQL 优化器怎么处理它。MySQL 处理 CTE 有两种策略。第一种叫物化Materialization就是真的把 CTE 的查询结果算出来存成一个内部的临时表后续引用直接读这个临时表。第二种叫合并Merge/ 内联相当于 MySQL 把 CTE 的定义展开当成普通子查询融进主查询里不产生中间存储。从执行效率上看没有绝对的好坏。物化适合结果集不大、但引用多次的情况因为只算一次合并适合结果集很大、但主查询能下推条件的情况因为可以提前过滤避免把一堆用不上的中间行物化出来。MySQL 会根据统计信息自己选但它不会每次都选对。这就要说到一个和直觉相悖的点CTE 不一定只执行一次。有些同学以为CTE 就像变量算一次缓存起来后面随便引用其实 MySQL 在特定情况下可能把同一 CTE 重算多次。所以别用缓存变量的思维去套它想知道实际怎么跑的看执行计划最靠谱。4.2 用 EXPLAIN 看执行计划别靠猜怎么看直接在 SQL 前面加 EXPLAIN然后看输出里的表名列。如果优化器选择了物化执行计划里通常能看到类似Materialized CTE的标记或者 CTE 名出现在table列如果选择合并CTE 名基本不会单独出现它被展开成普通表关联了。MySQL 8.0.18 以后还有 EXPLAIN ANALYZE可以直接看到每个节点的实际行数、耗时和循环次数。我第一次用 EXPLAIN ANALYZE 检查一个递归 CTE发现它把锚点部分跑了 N 次才知道自己 JOIN 条件写岔了导致递归反复扫描这个问题光看文本根本发现不了。实操建议是涉及 CTE 的慢查询至少做两件事。一是看执行计划确认 CTE 是物化还是合并二是看有没有出现Using temporary、Using filesort这类字样。物化本身就要写临时表如果 CTE 结果特别大再加上外层又排序磁盘压力会很可观。4.3 哪些场景我劝你慎用 CTECTE 不是万能药有几个场景我会刻意避开。一是 CTE 结果集特别大、而且只引用一次的这时候物化可能白花一次写临时表的成本不如直接用派生表让优化器决定合并策略有时候执行计划反而更干净。二是递归层级特别深、但业务上其实可以换个思路的比如组织架构超过几十层递归 CTE 每一层都要扫描一次树深度一大性能很难看有些场景用路径枚举或闭包表设计更稳。三是在 OLTP 高频小查询里堆 CTE明明一条索引就能解决的简单查询硬拆成三个 CTE反而是给优化器添乱。简单说CTE 用在复杂分析、报表、数据加工这些场景是加分项用在高频简单查询上属于杀鸡用牛刀。4.4 能不能干预优化器物化与合并的手动控制MySQL 8.0 提供了 optimizer hint可以在一定程度上干预 CTE 的策略。最常用的是 MERGE 和 NO_MERGE 两个提示写法是在 SELECT 后面以注释形式标注SELECT /* NO_MERGE(dept_avg) */ dept_no, avg_salary FROM dept_avg;NO_MERGE 表示让优化器别把 dept_avg 合并进主查询强制物化MERGE 则相反希望它尽量展开合并。不过我要提醒hint 在不同小版本上行为可能有差异而且强行干预也可能让优化器选到更差的路径。我的建议是先靠统计信息和索引把基础优化做扎实再用 EXPLAIN 对比最后才考虑加 hint。千万别一上来就 NO_MERGE 一把梭。另一个可调的参数是临时表的内存上限。MySQL 8.0 内部临时表默认用 TempTable 存储引擎内存有上限超过后落到磁盘。如果 CTE 物化结果远超内存阈值频繁落盘会拖慢整体性能。这个时候可以结合实际情况调大内存上限或者优化 CTE 内部的查询让结果集先瘦身而不是无脑加内存。5. 避坑指南我在生产环境踩过的雷5.1 版本坑5.7 项目里的灵异报错先讲最常见的。公司里有人把一段网上抄的 CTE 查询贴到 5.7 的库里执行报1064 - You have an error in your SQL syntax near WITH。他第一反应是 SQL 写错了来回改半天。其实原因只有一个5.7 根本不认识 WITH。MySQL 8.0 才开始支持 CTE所以遇到这种报错先SELECT VERSION();确认版本别浪费时间改语法。顺带提一下 MariaDB。MariaDB 10.2 也支持 WITH语法大体一致但递归 CTE 的细节和 MySQL 有差异比如某些子句限制不同。如果你在 MySQL 和 MariaDB 之间做迁移CTE 部分要专门做一轮回归测试别指望完全无缝。5.2 递归深度上限1000 次的隐形天花板递归 CTE 最经典的报错长这样ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing cte_max_recursion_depth.MySQL 默认把cte_max_recursion_depth设为 1000就是防止递归失控把服务器跑挂。你生成日期范围超过 1000 天或者组织树深了都会撞上这个天花板。解决办法是按需调大注意这个参数既可以全局设置也可以会话级设置SET SESSION cte_max_recursion_depth 10000;我个人的习惯是每个会话单独设不用全局值避免某个同事一段失控递归把整个实例拖垮。同时递归成员里一定要写清楚终止条件比如日期序列里的WHERE d 2025-01-31或者组织树里按 depth 限制层数。递归没有终止条件轻则报错重则资源耗尽。5.3 作用域和命名CTE 不是全局变量CTE 的生命周期只有一条语句。很多新手以为查完一条带 WITH 的 SELECT下一条语句还能继续用这个 CTE结果报Table cte_name doesnt exist。这是认知问题CTE 在当前语句结束时就被释放了它不是临时表也不是变量不能跨语句复用。命名上还有个隐蔽坑CTE 的名字如果和真实表名重名CTE 在语句内会遮蔽同名表。就是说你声明了一个叫employees的 CTE主查询里写 FROM employees实际用的是 CTE 而不是真实表。这种遮蔽有时候是故意的但更多时候是事故——你本意是查表结果命中了 CTE。我的建议是 CTE 命名遵循一套自己的前缀或风格比如业务缩写 语义别用和表名一模一样的名字。5.4 递归成员的语法限制ORDER BY 和聚合别乱放MySQL 对递归成员的限制比较严格我踩过的典型报错是在递归部分的 SELECT 里写了 ORDER BY 或者 LIMIT直接被拒。递归部分也不能用聚合函数、窗口函数这类东西。原因不难理解递归是靠迭代推进的每一轮都要产出新行给下一轮排序、限制、聚合这些操作语义上和迭代产生新行冲突。遇到这种需求标准解法很朴素递归只负责把树或序列铺出来排序、分页、聚合全部放到递归外面包一层 SELECT。比如先递归出完整部门树外层再ORDER BY、LIMIT或者在外层做汇总计数。这个套路记住之后递归 CTE 基本就稳了。5.5 临时表和磁盘大结果集物化的隐藏风险CTE 物化会用到内部临时表内存放不下就写磁盘默认临时目录如果空间不够查询会报错严重的直接把实例所在机器的磁盘写满。我遇到过一次大表关联后 CTE 结果集特别大临时文件把数据盘撑爆最后只能停机清理。应对思路有三个第一CTE 内部尽量先 WHERE、先聚合让结果集变小再物化第二关注tmpdir所在磁盘的空间监控临时表落盘量第三必要时用 NO_MERGE 或改写查询主动避免大结果物化。一句话CTE 的结果集越小越安全先把数据范围收窄再让 CTE 发挥可读性优势。6. 最后聊点实用心得我个人用下来最大的体会是CTE 真正的价值不在性能而在把复杂问题变成线性思考。以前写嵌套子查询眼睛要反复在内层和外层之间跳改用 WITH 之后一条 SQL 就是顺着读下来的几步命名起得清楚同事 review 都轻松。现在我做任何超过两层的数据加工第一反应就是拆 CTE而不是堆括号。还有个小技巧递归 CTE 不只是处理树和日期凡是需要逐层推导的逻辑都可以试试比如算斐波那契数列、做 BOM 物料层级展开、找推荐关系链路。这些需求以前要么写存储过程循环要么在应用层递归现在一条 SQL 就能表达面试里也经常被拿来考察对 8.0 新特性的掌握程度。最后提醒一句CTE 再方便也改变不了先有索引再有性能这个基本盘。CTE 里的 JOIN 和 WHERE该建索引还是得建。把 MySQL 8.0 升级到位再把 WITH AS 和窗口函数用熟处理报表和复杂查询的信心会完全不同。