Flux.1-Dev深海幻境创意作品展：致敬“达盖尔的旗帜”的现代摄影艺术
2026/3/23 17:49:29
MySQL CTE（Common Table Expression，公用表表达式）
是 MySQL 8.0（2018 年）引入的重要特性，极大提升了复杂查询的可读性、可维护性和复用性。
2026 年，几乎所有新项目和中大型系统都强烈推荐使用 CTE 来替代之前的子查询嵌套、临时表、自连接等写法。
一、CTE 到底解决了什么痛点？
| 写法对比 | 传统子查询 / 派生表写法 | 使用 CTE 后的写法 | 优势 |
|---|---|---|---|
| 可读性 | 多层嵌套，括号层层套娃，很难一眼看懂 | 像写函数一样，先定义再引用，结构清晰 | ★★★★★ |
| 代码复用 | 同一个子查询要重复写多次 | 定义一次，多次引用 | ★★★★★ |
| 递归查询 | 几乎不可能（需要存储过程或循环） | 原生支持 WITH RECURSIVE | ★★★★★ |
| 调试难度 | 改一层要检查所有嵌套层 | 可以单独 SELECT 每个 CTE 来验证 | ★★★★☆ |
| 性能 | 大多数情况下差不多（优化器会重写） | 部分场景略好（物化 CTE 在 8.0.18+ 更智能） | ≈ 或略优 |
二、CTE 基本语法（必须记住的模板）
WITHcte_name1AS(SELECT...-- 第一个公用表表达式),cte_name2AS(SELECT...FROMcte_name1WHERE...)SELECT...FROMcte_name2JOINcte_name1ON...WHERE...;关键点：
- WITH 写在 SELECT 之前
- 多个 CTE 用逗号分隔
- 后面的 CTE 可以引用前面的 CTE（顺序重要）
- 最后一个 CTE（或主查询）引用前面的
三、5 种最常见使用场景 + 代码示例（生产级）
1. 简化多层嵌套子查询（最常见用途）
需求：查询每个部门工资高于部门平均工资的员工
-- 传统写法（嵌套多层，可读性差）SELECTe1.*FROMemployees e1WHEREsalary>(SELECTAVG(salary)FROMemployees e2WHEREe2.dept_id=e1.dept_id);-- CTE 写法（清晰得多）WITHdept_avgAS(SELECTdept_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdept_id)SELECTe.*FROMemployees eJOINdept_avg dONe.dept_id=d.dept_idWHEREe.salary>d.avg_salary;2. 同一子查询多次使用（避免重复计算）
WITHrecent_ordersAS(SELECTorder_id,customer_id,order_dateFROMordersWHEREorder_date>=DATE_SUB(CURDATE(),INTERVAL30DAY))SELECTc.customer_name,COUNT(ro.order_id)ASorder_count,MAX(ro.order_date)ASlast_order_dateFROMcustomers cLEFTJOINrecent_orders roONc.customer_id=ro.customer_idGROUPBYc.customer_id,c.customer_name;3. 层级 / 递归查询（组织架构、评论回复、分类树等）
经典场景：查询员工及其所有下属（递归）
WITHRECURSIVE employee_hierarchyAS(-- 锚点成员（起点）SELECTemployee_id,name,manager_id,1ASlevelFROMemployeesWHEREmanager_idISNULL-- 最高领导UNIONALL-- 递归部分SELECTe.employee_id,e.name,e.manager_id,eh.level+1FROMemployees eINNERJOINemployee_hierarchy ehONe.manager_id=eh.employee_id)SELECTemployee_id,name,manager_id,levelFROMemployee_hierarchyORDERBYlevel,name;4. 分步计算复杂指标（报表常用）
WITHsales_by_dayAS(SELECTDATE(order_date)ASsale_date,SUM(amount)ASdaily_salesFROMordersGROUPBYDATE(order_date)),moving_avgAS(SELECTsale_date,daily_sales,AVG(daily_sales)OVER(ORDERBYsale_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)ASmoving_7d_avgFROMsales_by_day)SELECT*FROMmoving_avgWHEREsale_date>='2025-12-01';5. CTE 作为临时结果集 + 分页 / 去重 / 排名
WITHranked_productsAS(SELECTp.*,ROW_NUMBER()OVER(PARTITIONBYcategory_idORDERBYsalesDESC)ASrnFROMproducts p)SELECT*FROMranked_productsWHERErn<=3;-- 每个品类销量前 3四、MySQL 8.0+ CTE 重要特性与限制（2026 年须知）
| 特性 / 限制 | 说明 |
|---|---|
| 支持递归 CTE | WITH RECURSIVE（组织树、路径、图遍历等） |
| CTE 可多次引用 | 一个 CTE 被主查询和其它 CTE 多次使用 |
| CTE 可物化（Materialized CTE） | 8.0.18+ 优化器可能自动物化，重复使用时性能更好 |
| 不支持在 CTE 内使用 LIMIT | 除非在子查询中再包一层（常见绕法） |
| CTE 不能是 UPDATE/DELETE 的目标表 | 只能用于 SELECT（MySQL 限制，8.4 仍未放开） |
| 递归深度默认限制 | 1000 层（cmax_recursion_depth 可调，但慎用） |
五、2026 年生产建议总结（一句话版）
凡是子查询出现两次以上、嵌套超过两层、需要递归、报表分步计算，都优先考虑用 CTE 重写。
推荐进阶学习顺序：
- 普通 CTE（简化子查询）
- 多 CTE 组合 + 窗口函数
- WITH RECURSIVE（层级、路径、树）
- CTE + 窗口函数 + 聚合函数的复杂报表
- 观察 explain analyze 是否物化 CTE（性能调优）
有具体业务场景想用 CTE 重写吗？
贴出你现在的 SQL，我帮你改成更优雅的 CTE 版本。