MySQL CTE（Common Table Expression，公用表表达式）
是 MySQL 8.0（2018 年）引入的重要特性，极大提升了复杂查询的可读性、可维护性和复用性。
2026 年，几乎所有新项目和中大型系统都强烈推荐使用 CTE 来替代之前的子查询嵌套、临时表、自连接等写法。
一、CTE 到底解决了什么痛点？
| 写法对比 | 传统子查询 / 派生表写法 | 使用 CTE 后的写法 | 优势 |
|---|---|---|---|
| 可读性 | 多层嵌套，括号层层套娃，很难一眼看懂 | 像写函数一样，先定义再引用，结构清晰 | ★★★★★ |
| 代码复用 | 同一个子查询要重复写多次 | 定义一次，多次引用 | ★★★★★ |
| 递归查询 | 几乎不可能（需要存储过程或循环） | 原生支持 WITH RECURSIVE | ★★★★★ |
| 调试难度 | 改一层要检查所有嵌套层 | 可以单独 SELECT 每个 CTE 来验证 | ★★★★☆ |
| 性能 | 大多数情况下差不多（优化器会重写） | 部分场景略好（物化 CTE 在 8.0.18+ 更智能） | ≈ 或略优 |
二、CTE 基本语法（必须记住的模板）
WITH
cte_name1 AS (
SELECT ... -- 第一个公用表表达式
),
cte_name2 AS (
SELECT ... FROM cte_name1 WHERE ...
)
SELECT ...
FROM cte_name2
JOIN cte_name1 ON ...
WHERE ...;关键点：
- WITH 写在 SELECT 之前
- 多个 CTE 用逗号分隔
- 后面的 CTE 可以引用前面的 CTE（顺序重要）
- 最后一个 CTE（或主查询）引用前面的
三、5 种最常见使用场景 + 代码示例（生产级）
1. 简化多层嵌套子查询（最常见用途）
需求：查询每个部门工资高于部门平均工资的员工
-- 传统写法（嵌套多层，可读性差）
SELECT e1.*
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);
-- CTE 写法（清晰得多）
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
)
SELECT e.*
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;2. 同一子查询多次使用（避免重复计算）
WITH recent_orders AS (
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
)
SELECT
c.customer_name,
COUNT(ro.order_id) AS order_count,
MAX(ro.order_date) AS last_order_date
FROM customers c
LEFT JOIN recent_orders ro ON c.customer_id = ro.customer_id
GROUP BY c.customer_id, c.customer_name;3. 层级 / 递归查询（组织架构、评论回复、分类树等）
经典场景：查询员工及其所有下属（递归）
WITH RECURSIVE employee_hierarchy AS (
-- 锚点成员（起点）
SELECT
employee_id,
name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL -- 最高领导
UNION ALL
-- 递归部分
SELECT
e.employee_id,
e.name,
e.manager_id,
eh.level + 1
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
employee_id,
name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, name;4. 分步计算复杂指标（报表常用）
WITH
sales_by_day AS (
SELECT
DATE(order_date) AS sale_date,
SUM(amount) AS daily_sales
FROM orders
GROUP BY DATE(order_date)
),
moving_avg AS (
SELECT
sale_date,
daily_sales,
AVG(daily_sales) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_7d_avg
FROM sales_by_day
)
SELECT * FROM moving_avg
WHERE sale_date >= '2025-12-01';5. CTE 作为临时结果集 + 分页 / 去重 / 排名
WITH ranked_products AS (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
FROM products p
)
SELECT *
FROM ranked_products
WHERE rn <= 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 版本。
