说句实话，MySQL的WITH（公用表表达式，CTE）刚出来那两年，我周边很多同事都觉得它不过是“子查询换了个写法”，可有可无。直到后来接手一个报表模块，SQL动辄三四百行，一层套一层，改一个过滤条件要翻半天，最后连跑通都成了玄学。那时候我才真正意识到，WITH不是语法糖，它是把复杂查询拆成积木的关键工具。这篇文章我就把自己在实际项目里用WITH的经验、踩过的坑、以及不同场景下的写法一次说清楚。
1. 从一个传参风暴说起——WITH到底解决了什么问题
1.1 CTE的本质：把复杂查询拆成积木
公用表表达式最直观的理解，就是“在一条SQL里先定义几个命名的临时结果集，再让后面的查询反复引用它们”。它跟子查询的核心区别在于：子查询是嵌入式的，写完就消失，而且同一个逻辑被引用两次就得写两遍；CTE是声明式的，先定义好一个或几个“中间层”，主查询再像搭积木一样把这些中间层组合起来。
我举个实际场景。有个统计需求要算“每个品类的近30天销售额、近7天销售额、以及这两个数的比值”，如果不用CTE，你会写出一个超长嵌套：
SELECT category_id, SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 30 DAY THEN amount ELSE 0 END) AS sales_30d, SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 7 DAY THEN amount ELSE 0 END) AS sales_7d FROM orders GROUP BY category_id;这段看起来还能忍。但如果再加一层筛选：只要“近30天销售额超过10万”的品类，并且要关联商品主数据拿品类名称，你被迫要么写两次汇总、要么把整段结果塞进一个巨大的子查询。用CTE之后，逻辑就是平的：
WITH category_sales AS ( SELECT category_id, SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 30 DAY THEN amount ELSE 0 END) AS sales_30d, SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 7 DAY THEN amount ELSE 0 END) AS sales_7d FROM orders GROUP BY category_id ) SELECT c.category_name, cs.sales_30d, cs.sales_7d FROM category_sales cs JOIN category c ON c.id = cs.category_id WHERE cs.sales_30d > 100000;从可读性上看，这就是“一步拆一步”的思维。更重要的是，后面想加指标、改阈值，你只需要改其中一个CTE，而不是在大括号海里捞针。
1.2 哪些场景真正需要WITH
很多初学者喜欢“凡查询必CTE”，这没必要。我自己的判断标准是三选一满足才上：
第一，同一段逻辑在主查询里被引用两次以上。典型的例子是“算完平均值之后，再看哪些记录高于/低于平均”，这时候你至少需要两次引用聚合结果，子查询写两遍又丑又容易改漏。
第二，查询层级超过两层，并且中间结果有业务含义。比如“先清洗数据，再做聚合，再关联维表”，每一层都可以独立成CTE，方便单独调试。
第三，需要递归处理的层级结构。这类需求在MySQL 8.0之前只能靠存储过程、循环或者多次查询，CTE的递归写法是质变。
2. WITH基础语法：先把手上的积木搭起来
2.1 一个WITH可以挂多条CTE
先看最基本的语法框架。WITH可以定义一条CTE，也可以定义多条，多条之间用英文逗号分隔：
WITH cte1 AS ( SELECT id, name FROM user WHERE status = 1 ), cte2 AS ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) SELECT u.name, o.order_cnt FROM cte1 u LEFT JOIN cte2 o ON u.id = o.user_id;这里有两个容易忽略的细节。一个是我习惯给每个CTE的列起明确别名，尤其当CTE里的查询用了聚合函数、CASE表达式时，列名会变成COUNT(*)这种不方便后续引用的名字，显式加别名能省很多事。
另一个是CTE的引用顺序。MySQL允许后面的CTE引用前面的CTE，但前面的CTE不能引用后面的。也就是说，cte2可以引用cte1，反之不行。这个约束决定了你必须按照数据流动的方向来组织CTE顺序。
2.2 列别名的几个坑
WITH定义里的列名有两种写法。一种是在AS之后直接给子查询，列名由子查询决定；另一种是在CTE名称后面用括号显式指定列名：
WITH summary AS ( SELECT department, AVG(salary) AS avg_sal FROM employee GROUP BY department ) SELECT * FROM summary; -- 等价写法 WITH summary(department, avg_sal) AS ( SELECT department, AVG(salary) FROM employee GROUP BY department ) SELECT * FROM summary;第二种写法在CTE内部子查询很长、列名容易看花眼的时候特别好用，相当于提前给下游定义好接口。不过要注意，显式列名和你子查询里SELECT的列数量必须完全一致，多了少了都会直接报错。
另一个坑是CTE内部不能有重复的列名。比如你把两张表JOIN在一起，两张表都有id字段，一不小心SELECT *就会出现两个id，这时候主查询里引用cte.id就是模糊的。我的习惯是CTE内部尽量把字段名写全，并且对可能冲突的列做别名重命名，别抱着“反正最后能用”的侥幸心态。
3. 递归CTE：一条SQL啃下整棵层级树
3.1 递归CTE的语法结构
递归CTE是WITH RECURSIVE，它由两个部分拼成：一个基础查询（锚点），加上一个递归查询（迭代部分），两部分用UNION ALL连接。递归查询里会引用CTE自身，每一次迭代都在上一次结果的基础上继续扩张，直到不再产生新行为止。
WITH RECURSIVE cte_name AS ( -- 基础查询：第一层数据 SELECT ... FROM table WHERE parent_id IS NULL UNION ALL -- 递归查询：反复引用cte_name SELECT t.* FROM table t JOIN cte_name c ON t.parent_id = c.id ) SELECT * FROM cte_name;这个结构的执行逻辑很多人第一眼看不懂，我习惯用一个比喻：基础查询是“公司CEO”，递归查询是“拿着当前员工名单去查他们的下属”。第一次迭代拿到CEO，第二次迭代拿CEO的下属，第三次拿下属的下属，直到某次迭代查不出新人，递归停止。
3.2 组织架构树：从根节点往下铺
举一个最经典的例子：公司组织架构表，每行记录里有一个manager_id指向上级。要查一个完整部门树下所有人的名单和层级深度：
WITH RECURSIVE org_tree AS ( -- 锚点：找根节点 SELECT id, name, manager_id, 1 AS depth, CAST(id AS CHAR(500)) AS path FROM employee WHERE manager_id IS NULL UNION ALL -- 递归：找子节点 SELECT e.id, e.name, e.manager_id, ot.depth + 1, CONCAT(ot.path, ',', e.id) FROM employee e INNER JOIN org_tree ot ON e.manager_id = ot.id ) SELECT id, name, depth, path FROM org_tree ORDER BY depth, path;这段SQL里有几个小细节值得说。第一，depth字段用来记录层级深度，业务上凡是需要“第几级组织”的场景都离不开它。第二，path字段用CONCAT拼接了一条从根到当前节点的ID路径，排序时用path做字典序，能天然实现“同一个父节点下的子节点排在一起”的树状展示效果。第三，CAST(id AS CHAR(500))是为了避免递归时字段类型不一致导致报错，数字和字符串拼接之前必须统一类型，这个坑我在早期踩过不止一次。
假如你想反过来查某个员工的全部上级链，逻辑稍微换个方向：锚点变成自己，递归变成JOIN上级表，把parent_id改成id方向就行。递归CTE的方向由你的JOIN条件决定，这是最核心的抽象。
3.3 递归翻车的边界条件
递归CTE看起来爽，但翻车方式也很有规律。第一个大坑是死循环。如果数据里有环，比如A的上级是B、B的上级是A，递归就会无限扩张。MySQL对递归CTE有一个保护机制：默认最大递归深度为1000次（对应cte_max_recursion_depth变量），超过就报错。这能兜底，但1000层也可能卡死一段时间。所以递归查询里最好在业务层面约束迭代次数，或者先做去重和环检测，尤其当数据可能从外部系统同步过来时。
第二个坑是UNION ALL的性能问题。递归CTE每一次迭代都会查询原始表，层数深了会非常慢。如果数据量在百万级以上，递归CTE跑大层级树就很吃力。我在某公司做类目树同步时，蹭了5万条商品类目，递归超过了15层，单条SQL跑了快20秒，后来改成在应用层用循环构造树，一次查全量再内存构建，反而从秒级降到毫秒级。递归CTE适合层级可控、量级中等的场景，不是万能药。
第三个坑是path字段太长。递归拼接字符串时，如果树的深度大、节点ID长，path会膨胀得很厉害。MySQL对GROUP_CONCAT有max_allowed_packet的限制，但递归结果集不直接受这个限制，不过存储和排序的开销是实打实的。不需要展示路径时，就别画蛇添足加path字段。
4. 多条CTE协作实战：从清洗到聚合一条龙
4.1 多级CTE链：单条SQL处理完整业务链路
我在真实项目里最常见的CTE用法，不是单条CTE，而是“流水线式”的多条CTE链。场景是某个销售看板，需要统计“每个销售员当月业绩、业绩排名、以及是否进入前10%”。整个数据链路分三步：先关联订单和用户表，再按月聚合，最后打标。
WITH order_base AS ( SELECT o.order_id, o.salesman_id, o.amount, DATE_FORMAT(o.order_date, '%Y-%m') AS month_tag FROM orders o WHERE o.status = 'paid' AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH) ), monthly_sales AS ( SELECT salesman_id, month_tag, SUM(amount) AS total_amount, COUNT(DISTINCT order_id) AS order_cnt FROM order_base GROUP BY salesman_id, month_tag ), ranked_sales AS ( SELECT salesman_id, month_tag, total_amount, order_cnt, RANK() OVER (PARTITION BY month_tag ORDER BY total_amount DESC) AS rk, COUNT(*) OVER (PARTITION BY month_tag) AS total_salesman FROM monthly_sales ) SELECT s.name AS salesman_name, r.month_tag, r.total_amount, r.order_cnt, r.rk, CASE WHEN r.rk <= CEIL(r.total_salesman * 0.1) THEN 'TOP10%' ELSE 'NORMAL' END AS performance_tag FROM ranked_sales r LEFT JOIN salesman s ON s.id = r.salesman_id ORDER BY r.month_tag DESC, r.rk;这个例子完整展示了CTE链的价值：每一条CTE只做一件事，后一条CTE在前一条的结果上继续加工，到主查询时，剩下的只是关联维表和加业务标签。排查问题的时候，你可以把WITH后面任意一条CTE括号内的SQL单独拿出来跑一遍，看到底是哪一步数据不对，这种“单步可调试”的特性，是嵌套子查询完全无法比的。
4.2 CTE与窗口函数协同处理的几个细节
上面例子中RANK()和COUNT(*) OVER()这两个窗口函数，其实也体现了CTE与窗口函数配合的关键点。窗口函数的结果集和聚合结果集共享，当你需要“既保留明细行、又带出分组统计值”时，窗口函数是最优解，而CTE负责先把明细聚合好，让窗口函数在正确粒度的数据集上操作。
说个反例。如果不用CTE，直接在原始订单明细上做RANK() OVER (PARTITION BY salesman_id, month_tag ...)，你可能会遇到一个经典问题：订单明细有多行，同一个销售员的排名会被计算多次，而且不先聚合的话，SUM(amount)这种窗口函数作用在原始行上会产生重复计算。CTE在这里起到的是“清洗-聚合-计算”的职责分离作用，这也是我强调“一条CTE只做一件事”的原因。
另外一个细节是，窗口函数不能直接用在WHERE里过滤，这是SQL标准限制。如果你写了WHERE rk = 1会直接报错。正确思路是在CTE算完排名后，再包一层SELECT来过滤。CTE天然合适，因为它多定义一条引用前者的CTE即可：
WITH ranked AS ( SELECT salesman_id, month_tag, total_amount, RANK() OVER (PARTITION BY month_tag ORDER BY total_amount DESC) AS rk FROM monthly_sales ) SELECT * FROM ranked WHERE rk <= 5;4.3 CTE与INSERT/UPDATE：写数据也能用WITH
很多人以为CTE只能配SELECT，实际MySQL 8.0里WITH可以放在INSERT、UPDATE、DELETE前面。这个特性在数据归档和同步场景非常实用。
比如要把“近90天没有下单的用户”打上流失标签，并按规则写入用户标签表：
WITH inactive_users AS ( SELECT u.id FROM user u LEFT JOIN orders o ON o.user_id = u.id AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) WHERE o.id IS NULL AND u.status = 'active' ) UPDATE user_tag SET churn_flag = 1 WHERE user_id IN (SELECT id FROM inactive_users);这里要注意UPDATE子句后面不能再直接引用CTE名，必须用子查询绕一下，这是MySQL的一个语法限制。类似的还有DELETE，子查询里引用CTE没问题，直接DELETE FROM cte是不行的。
还有一类常见用法是“先查后插”的幂等同步：
WITH source_data AS ( SELECT product_id, SUM(amount) AS total_sales FROM order_detail WHERE order_date = '2025-01-01' GROUP BY product_id ) INSERT INTO daily_product_sales (product_id, sale_date, total_sales) SELECT product_id, '2025-01-01', total_sales FROM source_data src WHERE NOT EXISTS ( SELECT 1 FROM daily_product_sales d WHERE d.product_id = src.product_id AND d.sale_date = '2025-01-01' );这个写法的好处是，既避免了重复写入，又把整个同步逻辑收敛在一条SQL里。搭配游标多次执行也不会出现主键冲突，很适合批处理脚本。
5. WITH、子查询、临时表：选型不是玄学
5.1 可读性与维护性：子查询输得最惨
我见过不少老代码，几千行的报表SQL里全是派生表（FROM后面直接跟括号子查询）。这类代码的问题在于，你没法轻易看出“第一层算完什么、第二层在其基础上怎么算”。而CTE把中间结果“命名”了，命名就是文档。代码评审时，别人一眼看到WITH monthly_sales AS ...就知道这块是月度聚合，从monthly_sales再衍生出去，逻辑清晰很多。
有一个实践细节：我写CTE时会给中间结果起“能表达业务含义”的名字，而不是a、b、c这种无意义字母。比如paid_order_base、customer_rfm_score，这样主查询部分读起来几乎就是一段伪代码。后期接手的人（包括三个月后的自己）不用花时间去梳理哪个子查询对应哪个环节。
5.2 性能差异：CTE不一定物化
一个广为流传的说法是“CTE会有性能损耗因为要物化临时结果”。这个说法在MySQL里并不全对。MySQL 8.0的优化器对CTE的处理策略是可以被物化为临时表，也可以被内联展开（inline），具体看优化器估算。如果你在CTE上只引用一次，优化器大概率会把它当作一个视图内联展开，几乎没有额外开销；如果被引用多次，优化器可能物化以复用结果。
所以别听到“CTE慢”就因噎废食。真正的性能风险在于：被多次引用的CTE，如果内部查询很大、筛选条件很强，物化成临时表又没走对索引，那就是灾难。我在项目里遇到一次，一个CTE内部扫了几百万行，主查询里用了4次，最后这条SQL跑了30秒。排查后发现CTE物化成临时表之后没建索引，主查询关联它时每次都是全表扫描。解决办法也很直接：把主查询的关联条件尽量下推到CTE内部的WHERE里，同时考虑在CTE查询里先做GROUP BY或DISTINCT，把中间结果体积压到最小。
5.3 什么时候必须上临时表
CTE不是万能的。有两个场景我会直接创建临时表而不是用CTE：
一是同一个中间结果要被多条SQL共用。CTE的生命周期只在单条语句内，想跨语句复用，只能用临时表或者视图。
二是中间结果特别大、且需要多次过滤、多次关联时，临时表可以显式建索引。CTE物化出的临时表索引策略由优化器决定，受制于统计信息和内存参数，可控性不如自己建表。
CREATE TEMPORARY TABLE tmp_sales_summary AS SELECT seller_id, SUM(amount) AS total_amount FROM orders WHERE status = 'paid' GROUP BY seller_id; ALTER TABLE tmp_sales_summary ADD INDEX idx_seller (seller_id);临时表用完记得显式DROP TEMPORARY TABLE，或者在会话结束自动清理。这里再提醒一个细节：CREATE TEMPORARY TABLE ... AS SELECT在建表时不会把原表的索引带过来，需要手动加索引，而且ALTER TABLE临时表在MySQL里也是允许的，但要注意临时表的索引会影响写入开销，加索引前想清楚哪些字段真的需要用索引过滤。
6. 常见问题与排查技巧实录
6.1 递归死循环的防线
前面提过死循环问题，这里补一下具体经验。我的两个手段：先验数据里是否存在环，比如用WITH RECURSIVE查一遍原始表判断“存在至少一个节点能从自己出发回到自己”；如果环不可避免，就在递归CTE里加深度限制：
WITH RECURSIVE cte AS ( SELECT id, parent_id, 1 AS depth FROM tree_data WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id, c.depth + 1 FROM tree_data t JOIN cte c ON t.parent_id = c.id WHERE c.depth < 20 ) SELECT * FROM cte;WHERE c.depth < 20就是人为的“保险丝”。实际操作中我把这个数字定成“业务最大层级 + 5”，既能保证正常数据不截断，又能防止异常环把数据库跑死。另外，如果递归中你用了字符串拼接产生路径，可以用LOCATE(CONCAT(',', id, ','), CONCAT(path, ',')) = 0来防止同一条路径里重复出现节点，这也是一种简易去环手段。
6.2 CTE被多次引用，到底执行几次
这是论坛里最常见的月经问题。在MySQL 8.0里，优化器可能把CTE物化为内部临时表，然后多次引用时复用；也可能直接展开成子查询，在每次引用的地方各自执行。具体行为受optimizer_switch里的derived_merge和derived_condition_pushdown等参数影响，但作为使用者，你不应该假设它必定物化还是必定内联。
实际建议：如果一个CTE被引用多次且内部查询开销高，不如写个临时表，把执行策略握在自己手里。如果只引用一次，随意用CTE，几乎不会有害。我见过有人为了“避免重复执行”把CTE强行改成临时表，结果SQL里多了一条建表语句，运行环境还不允许建临时表，反而得不偿失。
6.3 命名冲突与作用域
CTE的作用域是整条语句，你不能在不同CTE里定义相同的名称，也不建议CTE名称和物理表名相同。一旦同名，MySQL会优先解析CTE，这会导致你本来想查物理表，结果拿到的却是CTE里的数据，排查起来特别隐蔽。
还有一种情况是CTE内部子查询里用了和CTE同名的表别名，在一些特殊写法下会造成解析歧义。我的习惯是CTE名称统一用模块名_含义，比如order_paid_base、seller_monthly_agg。物理表名+下划线+作用类型，最大限度避免撞名。
6.4 优化器不是万能的，CTE也可能被“扇耳光”
最后分享一个实际案例。有一个统计活跃用户的SQL，第一版用WITH把“用户基础表”定义出来，然后在主查询里多次JOIN它。上线后发现执行计划里这个CTE被物化成临时表，临时表又没有走索引，导致全表扫描了三次。我改了两处：一是在CTE内部先WHERE掉大部分历史分区数据，二是把主查询的关联字段调整为CTE里已经过滤过的高选择率字段。改造后SQL从9秒压到0.8秒。
这个案例的教训是：CTE的“逻辑复用”不等于“物理复用”，不管写得多清爽，最终执行计划才是真相。写完CTE后，务必EXPLAIN一下重点SQL，看看有没有意料之外的全表扫描，尤其在主查询多次引用CTE时，要留意Materialize这个关键字。
另外还有一个冷门但真实的坑：WITH后面的第一个关键字不能是SELECT的注释类型不对。有次同事在WITH前面加了一行以--开头的注释，结果MySQL解析时报错，因为WITH语句要求WITH是语句的第一个词，紧跟前方的注释在某些连接池参数下会引发解析问题。对应方案是注释放在WITH内部子查询里，或者放在整条SQL语句之后。
7. 我踩过几次坑后的几条心得
回头再看，CTE给我的最大改变不是省了几行代码，而是让我写SQL的思维方式从“一口气憋出最终结果”变成“先拆中间结果，再拼装”。尤其是多表联动、多层聚合的业务报表，用CTE把每个环节的产物显式命名，后面接需求的人能省下大量读码时间。
工具选型上，我的默认建议很简单：查询只在单条SQL内复用，优先CTE；需要跨语句复用或要对中间结果加索引，临时表；子查询只用于简单的一次性过滤，别让它承担复杂装配的职责。
最后分享一个日常排查小技巧。如果你负责维护一堆老SQL，看到几百行嵌套子查询的时候，别急着重写，先按照业务逻辑把它们拆成CTE链，一条条验证。第一步只拆不改逻辑，第二步再优化内部查询。这个过程不产生风险，但能让你对数据流转一目了然。真正把WITH用熟练后，你会发现SQL从“应付需求”变成“梳理业务逻辑”的工具，那种把握感，值得每个数据开发人员体验一次。
