如果你写SQL的时候还被三层嵌套子查询逼疯过那这篇值得看完。我入行做数据分析和后端开发那几年最怕接手那种 SELECT 里套 SELECT、WHERE 里再塞 EXISTS 的千层饼SQL。后来 MySQL 8.0 终于引入了 CTECommon Table Expression我才算真正感受到什么叫SQL 清爽神器。这篇我会从最基本的语法讲起但重点放在真实业务场景、性能真相和踩坑记录上目标很直接让你看完就能把以前那种绕来绕去的嵌套子查询改成一眼能读懂的 CTE而且知道什么时候该用、什么时候别硬用。1. 嵌套子查询的三大痛点为什么你读SQL会头疼说实话我刚开始干数据分析那会儿最怕的就是从前辈手里接手带着四五层括号的SQL。那玩意儿第一眼看过去确实能跑但你想改个指标、加一个过滤条件整个人就像拆炸弹一样。你可能觉得我在夸张但真正经历过线上报表数据对不上、凌晨还在扒拉一条三层子查询的人一定懂我说的滋味。1.1 可读性越套越深越看越懵嵌套子查询的本质是把一个查询的结果当作另一个查询的输入。这本身是很好的逻辑抽象但问题在于SQL的书写顺序和阅读顺序不是一回事。人在读SQL时习惯从上往下、从左往右但子查询往往是从里往外读。三层嵌套的时候你脑子里要维护三张虚拟表还要记住每一层引用的是哪张表、哪个别名。我见过最夸张的一次一个报表SQL嵌套了六层中间还混着 EXISTS、IN、NOT IN当时整个团队没人愿意动它因为动一处错三处。这里有一个容易被低估的点缩进。很多写嵌套子查询的人不缩进括号一多连哪里开始哪里结束都分不清。MySQL 又没有像 Python 那样强制的缩进语法所以等读到第30行时你根本不知道这个右括号到底是哪个子查询的。我后来给团队定的规矩是超过两层嵌套必须拆开写要么用临时表要么用视图要么直接上 CTE。1.2 调试中间结果取不出来嵌套子查询最大的硬伤是你没法单独验证中间结果。比如我要先算出每个部门的平均薪资再找出平均薪资超过公司整体均线的部门。如果直接写一句话嵌套查询我想看看第二步之前的中间结果怎么办只能把子查询单独拆出来复制到另一个查询编辑窗口跑完确认无误再塞回去。这个过程极度单调而且拆的时候还容易漏条件、写错别名。调试对于一个复杂的SQL太重要了。我相信很多人和我一样面对一个结果不对的查询第一反应不是猜而是想先看看中间数据长什么样。嵌套子查询在这个需求面前基本是无能为力的要么拆要么干脆相信运气。运气不好时你会陷入改一行、跑一次、结果还是不对的死循环最后发现是子查询里某个字段类型隐式转换导致索引失效这种消耗是最让人崩溃的。1.3 复用同样的逻辑只能复制粘贴一个真实场景报表里要计算本月新增客户这个指标你在一处写了带有 DISTINCT 和聚合的子查询。过两天另一个报表也要这个口径你没有别的办法只能把这段子查询再复制一份。复制粘贴本身问题不大可怕的是口径一改你要改的地方可能不止两处而是五处八处。漏改一处数据对不上又得花半天排查是不是缓存问题。复用问题还有另一个变种同一段SQL里同一个子查询可能要用两次。比如既要在 WHERE 里用它过滤又要在 SELECT 里用它做对比那就真的只能重复两遍。代码长度翻倍而且两遍写得不一致的风险也在翻倍。这种时候你要的不是能不能跑而是能不能干净地跑。2. CTE基础一句话理解一个骨架学会CTE 的全称是 Common Table Expression中文一般叫公共表表达式。它做的事情其实特别简单把一个查询先起个名字存在查询的最前面然后再去引用。用生活化的类比它就像你做饭之前先把葱姜蒜切好放在小碗里后面炒菜的时候随时取用而不是每次炒到一半再跑去切菜。理解这一点你就已经理解了 CTE 百分之七十的价值。2.1 什么是CTE和派生表有什么区别很多人第一次看 CTE 都觉得这玩意儿不就是派生表Derived Table吗也就是 FROM 后面的括号子查询。确实在功能上有重叠但使用体验差别很大。派生表必须嵌在 FROM 子句里所以你不能先声明一个派生表再在 WHERE 或者 SELECT 里引用它CTE 则是用 WITH 在语句最前面声明后面的整条语句都能用位置灵活得多。还有一个很重要的差异CTE 可以自我引用也就是递归。这是派生表永远做不到的。递归在遍历树形结构、生成序列、展开层级数据时是刚需后面我会展开讲。简单来说派生表是一次性工具CTE 是名正言顺的复用单元。MySQL 是从 8.0 开始正式支持 CTE 的8.0.1 之后语法就稳定了。如果你还在用 5.7抱歉CTE 用不了那只能继续忍受子查询或者想别的办法。现在8.0已经是主流该升级就升级。如果你负责老项目迁移这也可以作为升级 MySQL 的重要理由之一。2.2 基本语法与多个CTE一个最简单的 CTE 长这样WITH avg_salary_cte AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) SELECT * FROM avg_salary_cte;注意几个细节。第一WITH 后面跟的是 CTE 的名字名字之后用 AS括号里是完整的查询。第二主查询可以放在 SELECT、INSERT、UPDATE、DELETE 前面但最终结果还是由最外层语句决定。第三如果你有多个 CTE可以用逗号隔开后面一个可以引用前面一个。这种流水线式的写法才是 CTE 的精髓WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ), high_dept AS ( SELECT dept_id FROM dept_avg WHERE avg_salary 8000 ) SELECT e.emp_id, e.emp_name FROM employee e WHERE e.dept_id IN (SELECT dept_id FROM high_dept);这种后面的CTE引用前面的CTE的写法本质上就是在表达一个数据加工流水线。第一步算什么第二步基于第一步继续算什么每一步都有名字、有边界读起来跟读一个流程文档差不多。我经常给团队打比方这就像做菜谱第1步切菜第2步炒菜第3步装盘每一步都写清楚而不是把所有动作都塞进一个括号里。2.3 什么时候应该用CTE我的个人标准是下面几条满足任意一条就把子查询改成 CTE子查询数量超过一个嵌套层级超过两层同一个中间结果要被引用两次以上查询里需要递归遍历你希望别人包括三个月后的自己能快速看懂这段SQL。反过来如果一个非常简单的标量子查询就能搞定比如 WHERE salary (SELECT AVG(salary) FROM employee)那也没必要强行套个 CTE。过度封装同样是可读性杀手适度就好。我对适度的理解是CTE 把复杂变简单而不是把简单变复杂。如果一个查询十行以内能说明白直接写就好不需要为了用 CTE 而用 CTE。3. 从嵌套子查询到CTE一个改写案例看懂全部前面讲理论可能还不够直观我们直接来一个实际改造案例。我尽量模拟日常报表中会真实出现的需求而不是教科书里的清新例子。3.1 场景背景与原始SQL需求是找出部门平均薪资高于全公司平均薪资的部门里薪资最高的人是谁。这个需求脱胎于绩效分析实际工作中很常见。先看嵌套子查询版本SELECT e.emp_id, e.emp_name, e.dept_id, e.salary FROM employee e WHERE e.dept_id IN ( SELECT d.dept_id FROM ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) d WHERE d.avg_salary ( SELECT AVG(salary) FROM employee ) ) ORDER BY e.salary DESC LIMIT 1;这段 SQL 只有三层嵌套但你已经能感受到问题了第一个子查询在算部门平均薪资第二个标量子查询在算全公司平均薪资最外层又负责找薪资最高的人。如果你字段一多、过滤条件再复杂一点这玩意儿基本没法维护。3.2 改写为CTE的完整过程改成 CTE 之后我们把中间步骤拆开。先说清楚这个版本的改写重点在拆层至于组内排名的语义细节我下一章专门讲这里先看框架。WITH company_avg AS ( SELECT AVG(salary) AS avg_salary FROM employee ), dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ), target_dept AS ( SELECT d.dept_id FROM dept_avg d CROSS JOIN company_avg c WHERE d.avg_salary c.avg_salary ) SELECT e.emp_id, e.emp_name, e.dept_id, e.salary FROM employee e INNER JOIN target_dept t ON e.dept_id t.dept_id ORDER BY e.salary DESC LIMIT 1;这一步一步拆下来逻辑清楚多了先算公司平均线再算部门平均线再筛出高于公司平均线的部门最后拿员工表和目标部门做 JOIN。每一步 CTE 的职责都很单一一旦结果不对你可以单独运行其中任何一段去检查。这就像查一个复杂 bug你不需要盯着整个报错堆栈看而是把每一层函数单独测一遍很快就能定位问题。这个版本还有个隐藏的进阶点company_avg 只写了一次但它出现在整个查询的开头后面如果还要拿它和其他部门维度做对比随时可以再用。你不再需要为了算平均值把同一段 AVG 语句复制三遍。3.3 解读为什么CTE版本更爽说实话光从性能看这两个版本在 MySQL 优化器眼里很可能最终执行计划差别不大因为优化器会做子查询展开。真正拉开差距的是人力成本。CTE 版本有几个实打实的好处第一每一步都可以单独验证。我经常在 Navicat 里先把第一个 WITH 单独选中跑一遍确认 avg_salary 对不对再把第二个跑一遍。嵌套子查询做不到这件事你只能小心翼翼地复制。第二条件逻辑从括号嵌套关系变成了命名引用关系。人的工作记忆是有限的大概能同时记住七加减二件事。三个 CTE 名字调用彼此比三层括号叠加轻松太多。第三改需求的时候命中了单一改动点。比如公司平均线的计算口径变了从所有员工改成在职员工你只需要改 company_avg 这个 CTE 里的 WHERE 条件其他部分一点不用动。如果是在嵌套子查询里改你得先定位到第几行括号里的第几个小括号这个定位本身就是事故高发区。4. CTE进阶实战树形递归、分组TopN、滚动汇总如果 CTE 只是让代码好看一点那还不足以被称为神器。真正让 CTE 不可替代的是递归能力以及和窗口函数组合后的表现力。这一章我们从实际需求出发把三种高频场景都过一遍。4.1 递归CTE遍历组织架构先看最常见的递归场景组织架构树。假设 employee 表里有 manager_id 表示直属领导现在要拿到从 CEO 开始往下每一层的完整路径。WITH RECURSIVE org_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS depth FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, ot.depth 1 FROM employee e INNER JOIN org_tree ot ON e.manager_id ot.emp_id ) SELECT emp_id, emp_name, depth FROM org_tree ORDER BY depth;递归 CTE 的语法分三块初始查询锚点、递归查询、终止条件。锚点负责找到树根递归部分通过 JOIN 自己来一层层往下走终止条件由两个机制保证一是递归查询的结果集不断增长但路径总会在某个点走到尽头二是超出深度限制会报错。需要注意的关键点是 UNION ALL 和 UNION DISTINCT 的选择。树形结构不会出现重复路径用 UNION ALL 性能更好但如果你的关系数据有环比如 A 的上级是 BB 的上级又是 A那就必须做好环路检测否则会陷入死循环直到触发递归深度上限。我实际做组织架构树的时候还会在递归里加一个 depth 字段方便前端渲染成缩进结构。你也可以在递归里拼接 path 字段比如 CONCAT(ot.path, - , e.emp_name)这样每个节点能直接看到自己的完整汇报链。4.2 分组TopN窗口函数加CTE组合接着 3.2 遗留的问题如果我要找部门平均薪资高于公司平均线的每个部门中薪资最高的前两名单靠 CTE 拆分层级还不够因为 LIMIT 2 只能作用于最终结果集没法按部门分组取 TopN。解决办法是 CTE 加窗口函数WITH target_dept AS ( SELECT dept_id FROM employee GROUP BY dept_id HAVING AVG(salary) ( SELECT AVG(salary) FROM employee ) ), ranked AS ( SELECT e.emp_id, e.emp_name, e.dept_id, e.salary, ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn FROM employee e INNER JOIN target_dept t ON e.dept_id t.dept_id ) SELECT emp_id, emp_name, dept_id, salary FROM ranked WHERE rn 2;这段已经把我要的每个部门各自的前两名并且这些部门还得满足平均薪资高于公司平均线完全表达清楚了。先 HAVING 筛部门再 ROW_NUMBER 在部门内排名最后 WHERE rn 2 取前两。没有一层多余的嵌套每一步都是独立的命名块。窗口函数里的 PARTITION BY 和 ORDER BY 是关键。PARTITION BY 指定分组维度ORDER BY 指定排序规则ROW_NUMBER() 在这个窗口内从 1 开始递增。如果你希望并列的名次也占上榜名额可以换成 RANK() 或 DENSE_RANK()这是我踩过坑的地方排行榜需求里相同成绩并列和相同成绩顺延是两种完全不同的口径。RANK() 会在并列后跳号比如 1、1、3DENSE_RANK() 不跳号是 1、1、2。决定用哪个函数必须和业务方确认清楚。4.3 累计汇总与按月滚动统计做经营分析时另一个高频能力是滚动累计。比如按月份统计销售额再算一个截至当月的历史累计值。CTE 可以先按月聚合再在外面用窗口函数做累计WITH monthly AS ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE order_date 2024-01-01 GROUP BY DATE_FORMAT(order_date, %Y-%m) ) SELECT month, total_amount, SUM(total_amount) OVER (ORDER BY month) AS running_total FROM monthly ORDER BY month;这里的关键是 SUM(...) OVER (ORDER BY month)。在没有 GROUP BY 的窗口语境下它会在所有行之间做累计ORDER BY 决定累加顺序。CTE 的作用是先把原始的明细订单流转成月度汇总流让外层的窗口函数专注做累计这一件事。你甚至可以用 CTE 构建一个完整的日期序列然后用 LEFT JOIN 把缺失月份补零。生成日期序列本身就用到递归 CTEWITH RECURSIVE all_months AS ( SELECT DATE(2024-01-01) AS month_start UNION ALL SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM all_months WHERE month_start DATE(2024-12-01) ) SELECT month_start FROM all_months;这个技巧在没有日历表的环境里尤为好用。以前要先生成一张临时数字表现在一个递归 CTE 就搞定而且写在同一段 SQL 里语义非常清晰。把日期主表和业务数据 LEFT JOIN再配合 COALESCE 补齐缺失月份的值一份连续的月度趋势图就出来了。4.4 数据去重检查的实际案例最后说一个我非常常用的场景按某个业务键查重。登录日志表里同一个用户同一天可能因为各种原因产生多条记录要去重只保留最新一条。先查出重复记录WITH ranked AS ( SELECT id, user_id, login_time, ROW_NUMBER() OVER ( PARTITION BY user_id, DATE(login_time) ORDER BY login_time DESC ) AS rn FROM user_login_log ) SELECT id, user_id, login_time FROM ranked WHERE rn 1;当你想删除这些重复记录时稳妥做法是先用上面的 SELECT 确认影响范围再执行 DELETE。我个人的经验是不要贸然直接在 DELETE 里写窗口函数虽然 MySQL 8.0 语法上允许 WITH 和 DELETE 配合但版本不同、行为细节有差异线上谨慎为上。最稳妥的做法是把重复 id 集合捞出来再配合条件删除。CTE 的价值同样体现在这里你可以先在 SELECT 分支里反复验证 CTE 的逻辑确认无误后再把整套 SQL 挪到 DELETE 场景排查成本低很多。5. CTE性能真相与常见坑别把语法糖当银弹看到这里很多朋友会自然产生一个问题CTE 这么好用性能会不会有问题我的回答是CTE 并不是银弹它主要解决的是人读不懂的问题不是数据库跑得慢的问题。这一章我们聊聊你必须知道的性能真相和坑。5.1 CTE性能和嵌套子查询比到底如何先说结论在绝大多数场景下CTE 和嵌套子查询的执行计划是等价的。MySQL 优化器在生成执行计划时会把 CTE 当作派生表或者内联视图来处理能合并的就合并不能合并的就物化成临时表。所以如果你把一段嵌套子查询原样改写成 CTE除非优化器做了不同选择否则性能不会翻天覆地地变好也不会明显变差。那 CTE 有没有可能更慢有。当 CTE 被引用多次时MySQL 可能选择物化 CTE 的结果也就是把中间结果落地到内存中的临时表。这个物化过程有成本如果中间结果集特别大物化成本反而可能高于重复扫描原表。早年我做过一个报表把一个 CTE 引用了三次数据量几百万行结果物化开销明显。后来我把 CTE 拆成不同的查询分别跑配合索引优化总耗时才降下来。所以正确的态度是CTE 是可读性工具不是性能优化工具。很多慢 SQL 的根因在表结构、索引、JOIN 策略上把这个锅甩给 CTE 是不公平的。5.2 多次引用CTE的物化问题关于多次引用我再说细一些。如果你在一个查询里对同一个 CTE 引用了两次MySQL 通常不会把这段 CTE 重复执行两遍而是倾向于先物化成临时表再复用。这听起来是好事但也带来两个隐患。第一物化临时表没有索引除非 MySQL 自动建立索引否则你在外层多次 JOIN 这个 CTE 的时候可能要走全表扫描速度比直接查原表慢。第二物化需要临时空间在内存不够时会写到磁盘一旦写盘性能下降成指数级。我自己观察过 behavior一个 500 万行的 CTE内存模式下可能几十毫秒一旦转成磁盘临时表直接秒级起步。我自己的习惯是同一个 CTE 如果要用两次以上我就会认真看 EXPLAIN看它到底是 merge 还是 materialize。如果物化了而且结果集很大我会考虑能否把这段逻辑独立成一张正式表或者用临时表手动物化并加索引。CTE 写起来清爽不等于可以随便写个大的。5.3 递归CTE的四个常见错误递归 CTE 是写作事故的高发区我总结四个高频错误。第一个错误是递归部分忘了加终止条件或者条件写错导致无限递归。MySQL 有一个保护机制cte_max_recursion_depth默认是 1000超过就报错。如果你确实需要递归很深可以临时调大但一般超过 1000 就要反思数据模型了。SET SESSION cte_max_recursion_depth 5000;第二个错误是递归部分使用了聚合函数或窗口函数。CTE 递归部分的 SELECT 不允许使用聚合函数和窗口函数这是语法层面的限制不是优化层面的。假设你想在递归里统计每层人数对不起写不了你得先递归出所有节点再在外面做聚合。第三个错误是 UNION 和 UNION ALL 用错。关系数据有环的时候UNION ALL 会让递归无限延伸必须做好环路检测或用去重语义。树形结构没有环才可以用 UNION ALL而且在语义上保留所有路径才是你真正想要的这时候贸然上 UNION DISTINCT 还可能意外丢数据。第四个错误是搞不清锚点查询和递归查询的顺序。递归 CTE 的结构必须先是锚点查询然后 UNION [ALL/DISTINCT]最后是递归查询。递归查询里一定要引用 CTE 本身否则它不是递归就只是一个普通查询。很多人把锚点和递归部分写反了结果跑出来只有一行怎么查都查不全。5.4 版本支持与线上检查版本支持这块很少有人写清楚。MySQL 8.0.1 开始支持 CTE8.0.14 前后语法趋于完善但如果你连接的是 MariaDB版本支持情况又不一样MariaDB 支持 WITH 但语法细节可能有差异。上线之前我习惯做三件事确认生产环境 MySQL 大版本 8.0执行 EXPLAIN 看 CTE 是被 merge 还是 materialize在测试环境造一份接近真实规模的数据跑一遍观察临时表大小和耗时。另外要提醒一个现实的点很多公司有 SQL 审查工具或者团队规范对线上查询能写什么、不能写什么有纪律性约定。动手重构之前先问一下团队约定别把好工具变成了吵架的理由。毕竟工具只是手段团队协作和线上稳定才是目的。6. 常见问题与排查技巧实录最后这部分我把自己维护 SQL 过程中遇到的高频问题整理成速查形式方便你直接照着排查。6.1 报错速查表报错信息常见原因解决思路You have an error in your SQL syntax... near WITH版本低于 8.0 或写错位置确认 MySQL 版本把 WITH 放在整条语句最前面Recursive query without termination递归部分缺少终止条件或条件不可达检查递归 WHERE 条件的表达式确保能收敛Recursive Common Table Expression cant contain aggregate function递归部分使用了聚合/窗口函数把聚合挪到外层递归里只做行级展开Cant create temporary table物化临时表过大内存磁盘都撑不住该 CTE 逻辑物化太贵考虑建实体表或重构Exceeded max recursion depth超过 cte_max_recursion_depth 默认 1000先确认数据没有环再按需调大会话参数这个表并不能覆盖所有场景但覆盖了我和团队同事踩过的八成问题。遇到没见过的报错第一件事永远是单独运行 CTE 的各个子查询缩小问题范围。千万别在一条几百行的 SQL 里从头到尾猜是哪里的括号没闭合。6.2 优化与排查清单排查复杂查询时我习惯按下面顺序走而不是直接抓瞎优化先看 EXPLAIN 的 type 列是否存在全表扫描重点关注被 CTE 引用的主表有没有走索引单独跑一遍 CTE 里的查询确认中间结果量级如果中间结果有几百万行后续 JOIN 的性能就要警惕检查 JOIN 字段的类型和字符集是否一致不一致会导致索引失效这个坑常出在跨库 JOIN 上如果 CTE 被引用多次观察临时表大小必要时用优化器相关参数做微调最终用 profiling 或 performance_schema 对比改造前和改造后的耗时用真实数据说话。这里单独说一下 EXPLAIN。在 MySQL 8.0 里执行 EXPLAIN SELECT ... FROM 一个引用 CTE 的查询你会看到 CTE 出现在 derived table 相关行里。如果你看到 Materialize 关键字说明它物化了如果看到 Using temporary 也不要慌关键看数据量和是否走索引。只有把这些细节都掌握清楚你才能真正做到又快又清。6.3 我的一些真实体会文章写到这里最后分享一点我个人的操作习惯。我现在写任何查询默认流程都是先拆解业务逻辑再画信息流最后写 SQL。信息流里的每一个中间节点我基本都会写成 CTE。这么做的好处是SQL 越来越像一份可以读给业务同事听的执行说明。有一次需求评审我直接把 SQL 里的 CTE 名称和注释念给业务方听对方居然能边听边对口径这是嵌套子查询永远不可能实现的事。CTE 也不是万能的。真遇到特别复杂的 ETL 逻辑我依然会用临时表分步落库而不是硬塞一个超级大的 WITH 语句。清洗了大量数据之后你会发现工具的关键不在于能不能用而在于在什么粒度上用。CTE 的粒度适合把一个查询内部拆清楚跨查询、跨任务的时候就交给视图、临时表和存储过程。如果你们团队还没用上 CTE我强烈建议从重构一条模糊的子查询开始看看跑完再给别人读的效果。清爽不仅是一种感觉它直接决定了你半小时后开会时能不能讲清楚这段数的逻辑。