首页 / MySQL 入门教程 / 公用表表达式CTE

← 上一篇 下一篇 →
MySQL 入门教程

公用表表达式CTE

本教程共 46 篇 · 第 30 篇 · 更新于 2026-07-30 · 约 13 分钟阅读

MySQLMySQL 入门教程CTE公用表表达式WITH递归查询Recursive CTE层级数据

30. 公用表表达式 CTE

本节目标：理解公用表表达式（CTE）的概念与价值，掌握非递归 CTE 的 WITH 写法，学会用递归 CTE 处理层级树形数据（如组织架构、菜单树），知道 CTE 与子查询/派生表的取舍。

Warning

CTE 是 MySQL 8.0+ 才支持的功能。5.7 及更早版本不支持。本章所有示例基于 MySQL 26.7.0，在 8.0+ 环境下均可运行。
参考文档：MySQL 8.0 WITH (Common Table Expressions)

30.1 什么是 CTE

前面学了子查询和派生表。当一条 SQL 里需要反复引用同一个子查询时，代码会又长又难读——你得把同一段 SELECT 抄好几遍。

公用表表达式（Common Table Expression，CTE） 就是给子查询起个名字，后面可以反复引用。说白了，它是一张”临时的命名结果集”，只在当前语句执行期间存在。

CTE 用 WITH 关键字定义：

WITH cte_name AS (
    -- 子查询定义
    SELECT ...
)
-- 主查询引用 cte_name
SELECT ... FROM cte_name ...;
Note

CTE 和派生表（Derived Table）功能类似，但 CTE 优势在于：可以多次引用自身、支持递归、代码可读性更好。

30.2 非递归 CTE

先看一个简单场景：找出订单总额大于平均值的用户。

不用 CTE 的写法（派生表 + 子查询，要写两遍）：

SELECT u.username, total
FROM users u
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE o.total > (SELECT AVG(amount) FROM orders);

用 CTE 的写法：

WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
),
avg_order AS (
    SELECT AVG(amount) AS avg_amount FROM orders
)
SELECT u.username, ut.total, ao.avg_amount
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
CROSS JOIN avg_order ao
WHERE ut.total > ao.avg_amount;

逻辑清晰多了：user_totals 算每个用户总额，avg_order 算全局平均，最后 JOIN 比较。

一个 WITH 定义多个 CTE

逗号分隔，后面的 CTE 可以引用前面定义的：

WITH
    user_totals AS (
        SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
    ),
    top_users AS (
        SELECT user_id, total FROM user_totals WHERE total > 1000
    )
SELECT u.username, t.total
FROM top_users t
JOIN users u ON u.id = t.user_id;
Tip

一个 WITH 下可以定义多个 CTE，用逗号分隔。后续 CTE 可以引用前面定义的 CTE，但不能反向引用。

30.3 递归 CTE

递归 CTE 是 CTE 最强大的能力：它可以引用自己。用来处理层级、树形数据特别顺手。

语法结构

WITH RECURSIVE cte_name AS (
    -- 锚点成员：初始行（递归起点）
    SELECT ...
    UNION ALL
    -- 递归成员：引用 cte_name 自身
    SELECT ... FROM cte_name ...
)
SELECT * FROM cte_name;
  • 锚点成员（Anchor Member）：返回初始结果集，作为递归起点。
  • 递归成员（Recursive Member）：引用 CTE 自身，逐层迭代，直到返回空集停止。
  • UNION ALL 连接两者（也可用 UNION 去重）。

示例：组织架构树

假设有员工表 employees：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT  -- 上级经理的 id，根节点为 NULL
);

查询某个员工的所有下属（包括间接下属）：

WITH RECURSIVE subordinates AS (
    -- 锚点：从指定员工开始
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 1  -- 比如从 id=1 的 CEO 开始

    UNION ALL

    -- 递归：找 manager_id 在已有结果集中的员工
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, id;

执行过程：

  1. 锚点先返回 id=1 的 CEO（level=0）
  2. 递归第一轮：找到 manager_id=1 的员工（level=1）
  3. 递归第二轮：找到 manager_id 在上一轮结果中的员工（level=2）
  4. 直到某轮返回空集，递归停止
Warning

递归 CTE 必须确保能终止。如果数据存在循环（A 的上级是 B，B 的上级又是 A），会无限递归。MySQL 默认有 cte_max_recursion_depth 限制（默认 1000），超过会报错。生产环境建议检查数据是否有环。

生成数字序列

递归 CTE 一个常见用法是生成连续数字：

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

生成 1 到 100 的连续整数。这在生成日期序列、填充缺失日期时很实用。

30.4 CTE vs 子查询 vs 派生表

对比项CTE (WITH)派生表 (Derived Table)子查询 (Subquery)
可读性好，逻辑分层清晰一般，嵌套多了难读一般
多次引用可以，定义一次引用多次不行，每次要重写不行
递归支持（WITH RECURSIVE）不支持不支持
性能与派生表相当，MySQL 8.0.21+ 引入更多物化优化，实际行为由优化器决定每次引用重新执行视情况而定
版本要求8.0+全版本全版本
Tip

选择建议：需要多次引用同一结果集 → CTE；需要递归处理层级 → CTE；一次性简单过滤 → 子查询/派生表即可。

30.5 CTE 使用要点

  1. CTE 是临时的：只在定义它的那条语句（SELECT/INSERT/UPDATE/DELETE）执行期间存在，语句结束就消失。
  2. 不能嵌套定义 WITH：不能在 CTE 的 AS 子句里再写一个 WITH，但可以在外层语句定义多个 CTE。
  3. 递归深度限制：cte_max_recursion_depth 默认 1000，需要更大深度时调整： 
    SET cte_max_recursion_depth = 10000;
  4. CTE 内部可以引用前面定义的 CTE，但不能反向引用。
  5. 性能：MySQL 8.0.21+ 引入了更多 CTE 物化优化，实际执行策略由优化器决定。如果 CTE 被引用多次且内部查询复杂，注意观察执行计划，必要时用 EXPLAIN 确认是否走了临时表。
上一篇
窗口函数
下一篇
主键约束