公用表表达式（CTE）

约 854 字大约 3 分钟

布欧-Lewyon

2026-05-15

公用表表达式（Common Table Expression，CTE）是 MySQL 8.0 引入的一种临时命名结果集，用于替代复杂的子查询和派生表，让 SQL 可读性大幅提升。

基本语法

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

示例：各部门平均薪资，再筛选 > 12000 的

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employee
    GROUP BY dept_id
)
SELECT d.name AS dept_name, da.avg_sal
FROM dept_avg da
JOIN department d ON da.dept_id = d.id
WHERE da.avg_sal > 12000;

与之等价的子查询写法：

SELECT d.name, da.avg_sal
FROM (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employee
    GROUP BY dept_id
) da
JOIN department d ON da.dept_id = d.id
WHERE da.avg_sal > 12000;

CTE 版本从"先定义 dept_avg，再使用"的阅读顺序更符合人的思维。

多个 CTE

一个 WITH 可定义多个 CTE，用逗号分隔：

WITH
    dept_stats AS (
        SELECT dept_id,
               COUNT(*) AS cnt,
               AVG(salary) AS avg_sal
        FROM employee
        GROUP BY dept_id
    ),
    top_earner AS (
        SELECT name, dept_id, salary
        FROM employee
        WHERE salary > 20000
    )
SELECT d.name, ds.cnt, ds.avg_sal, te.name AS top_name, te.salary
FROM department d
LEFT JOIN dept_stats ds ON d.id = ds.dept_id
LEFT JOIN top_earner te ON d.id = te.dept_id;

CTE 引用其他 CTE

CTE 之间可以互相引用（后定义的可以引用先定义的）：

WITH
    dept_avg AS (
        SELECT dept_id, AVG(salary) AS avg_sal
        FROM employee
        GROUP BY dept_id
    ),
    above_avg AS (
        SELECT e.name, e.salary, da.avg_sal
        FROM employee e
        JOIN dept_avg da ON e.dept_id = da.dept_id
        WHERE e.salary > da.avg_sal
    )
SELECT * FROM above_avg;
-- 薪资高于部门平均水平的员工

递归 CTE（Recursive CTE）

递归 CTE 是 MySQL 8.0 的重大特性，适用于树形/层级数据（组织架构、分类树、评论回复等）。

语法：

WITH RECURSIVE cte_name AS (
    -- 非递归部分：初始行（锚点）
    SELECT ...
    UNION ALL
    -- 递归部分：引用自身
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

示例：生成数字序列

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
-- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

示例：组织树查询

CREATE TABLE org (
    id        INT PRIMARY KEY,
    name      VARCHAR(50),
    parent_id INT
);

INSERT INTO org VALUES
    (1, 'CEO', NULL),
    (2, 'CTO', 1),
    (3, 'CFO', 1),
    (4, '技术经理', 2),
    (5, '产品经理', 2),
    (6, '前端开发', 4),
    (7, '后端开发', 4);

-- 查询 CEO 下的所有下属（含多级）
WITH RECURSIVE org_tree AS (
    -- 锚点：从 CEO 开始
    SELECT id, name, parent_id, 0 AS level
    FROM org
    WHERE id = 1

    UNION ALL

    -- 递归：找子节点
    SELECT o.id, o.name, o.parent_id, t.level + 1
    FROM org o
    JOIN org_tree t ON o.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY level, id;
-- id | name       | level
-- 1  | CEO        | 0
-- 2  | CTO        | 1
-- 3  | CFO        | 1
-- 4  | 技术经理   | 2
-- 5  | 产品经理   | 2
-- 6  | 前端开发   | 3
-- 7  | 后端开发   | 3

递归限制

MySQL 默认递归最大深度为 1000，可通过 cte_max_recursion_depth 调整：

SET SESSION cte_max_recursion_depth = 10000;

CTE vs 派生表 vs 临时表

方案	优点	缺点
派生表（`FROM` 子查询）	无需额外定义	嵌套深时难读，无法多次引用
CTE	可读性好，可多次引用，支持递归	仅单条 SQL 内有效
临时表（`CREATE TEMPORARY TABLE`）	跨语句复用，可加索引	需显式创建和清理

小结

CTE 用 WITH 定义，让 SQL 像"先定义再使用"一样自然，替代深层嵌套的子查询。
同一个 WITH 可定义多个 CTE，后定义的可以引用先定义的。
WITH RECURSIVE 用于树形/层级数据的遍历，例如组织架构、分类树、菜单等。
递归 CTE 由锚点 + 递归部分（UNION ALL）组成，默认最大深度 1000。