MySQL 8.0 WITH AS 语法详解：从子查询到递归CTE的实战指南

写 SQL 写到想摔键盘，十有八九是栽在子查询嵌套上。我说的不是 WHERE 里面简单加个 IN，而是 FROM 里套一层、外面再套一层，三层起步那种意大利面式写法。前阵子接一个报表需求，逻辑其实不算复杂：先按部门算平均工资，筛掉低于公司平均线的部门，再跟上季度做环比。用嵌套子查询写出来，别说同事，我自己过两天再看都不知道哪段是干嘛的。最后全改用 WITH AS 重写，四五层嵌套变成自上而下念的三段，问题瞬间清爽。所以这篇文章想把 MySQL 的 WITH AS（官方叫 Common Table Expression，CTE）从语法到实战、从性能到避坑，一次讲透。适合刚接触 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 写复杂查询的两把钥匙。

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，缺失的日期自然会补出来，销售额为空的置为 0：

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' ), 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 哪些场景我劝你慎用 CTE

CTE 不是万能药，有几个场景我会刻意避开。

一是 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' doesn't 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 和窗口函数用熟，处理报表和复杂查询的信心会完全不同。