本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
进阶查询（窗口函数 / CTE / 递归 / 子查询） · CTE 与递归查询

什么是 CTE（Common Table Expression）？相比子查询有何优势？

What is a CTE (Common Table Expression) and what are its advantages over subqueries?

答案语言

考察要点

这道题面试官想看你是否真正理解 SQL 的演进和复杂查询的组织方式。核心考察点有三个：

  1. 代码组织与可读性：你是否能写出易于理解和维护的复杂 SQL。
  2. 查询复用能力：你是否知道如何在一个查询中多次利用同一份中间结果集，避免重复计算。
  3. 高级查询能力：你是否掌握了 CTE 独有的递归查询功能，以解决层级关系、路径查找等问题。

核心概念

CTE（Common Table Expression，公用表表达式）是一个在单个 SQL 语句（SELECT, INSERT, UPDATE, DELETE）的执行范围内定义的命名临时结果集。它通过 WITH 子句来定义，可以极大地提高复杂查询的可读性和可维护性。

基本语法结构：

sql
 1WITH cte_name (column1, column2, ...) AS (
 2    -- 定义 CTE 的查询
 3    SELECT ...
 4)
 5-- 主查询，可以引用 cte_name
 6SELECT * FROM cte_name;

CTE 与子查询（Subquery）的核心对比：

特性CTE (公用表表达式)子查询 (Subquery / Derived Table)
可读性高。采用自上而下的线性写法，将复杂逻辑拆解成独立的、命名的步骤，符合人的阅读习惯。低。层层嵌套，需要由内向外阅读，当嵌套超过 2-3 层时，逻辑变得非常混乱。
可复用性高。在单个查询中，一个 CTE 可以被后续的 CTE 或主查询引用多次。低。每次使用都必须重写一遍子查询的定义，导致代码冗余，且数据库可能需要重复计算。
递归能力支持。这是 CTE 的独有特性，可以非常优雅地处理树形或图结构数据。不支持。无法实现递归查询。
定义位置在主查询之前，使用 WITH 关键字。嵌入在 FROM, WHERE, SELECT 等子句中。
版本支持SQL:1999 标准。MySQL 8.0+, PostgreSQL, SQL Server, Oracle 等现代数据库均支持。MySQL 5.7 及以下版本不支持。几乎所有版本的 SQL 数据库都支持。

示例 1：可读性与复用性对比

场景：计算每个部门中，工资高于该部门平均工资的员工。

子查询解法（可读性差，重复计算 avg_salary）

sql
 1SELECT
 2    e.employee_name,
 3    e.department_id,
 4    e.salary
 5FROM
 6    employees e
 7JOIN
 8    (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) dept_avg
 9ON
10    e.department_id = dept_avg.department_id
11WHERE
12    e.salary > dept_avg.avg_sal;

如果还需要在 SELECT 列表里展示部门平均工资，子查询需要再写一遍或使用更复杂的结构。

CTE 解法（逻辑清晰，可复用）

sql
 1WITH department_avg_salary AS (
 2    -- 步骤1: 计算每个部门的平均工资
 3    SELECT department_id, AVG(salary) AS avg_sal
 4    FROM employees
 5    GROUP BY department_id
 6)
 7-- 步骤2: 关联员工表和部门平均工资表，进行筛选
 8SELECT
 9    e.employee_name,
10    e.department_id,
11    e.salary,
12    das.avg_sal -- 直接复用 CTE 的结果
13FROM
14    employees e
15JOIN
16    department_avg_salary das ON e.department_id = das.department_id
17WHERE
18    e.salary > das.avg_sal;

示例 2：递归查询（子查询无法实现）

场景：查询公司组织架构中，某个经理（如 id=1）下的所有直接和间接下属。

员工表 employees 结构:

idnamemanager_id
1老板NULL
2部门A总监1
3部门B总监1
4员工A12
5员工A22
6员工B13

递归 CTE 解法 (✅ MySQL 8.0+, PostgreSQL, SQL Server)

sql
 1WITH RECURSIVE subordinates AS (
 2    -- 1. 锚点成员 (Anchor Member): 找到起始点，即 ID=1 的直接下属
 3    SELECT id, name, manager_id, 1 AS level
 4    FROM employees
 5    WHERE manager_id = 1
 6

 7    UNION ALL
 8

 9    -- 2. 递归成员 (Recursive Member): 关联员工表和自身，不断向下查找
10    SELECT e.id, e.name, e.manager_id, s.level + 1
11    FROM employees e
12    JOIN subordinates s ON e.manager_id = s.id -- 关键：与 CTE 自身连接
13)
14-- 3. 主查询: 从递归 CTE 中查询所有结果
15SELECT * FROM subordinates;

输出结果：

idnamemanager_idlevel
2部门A总监11
3部门B总监11
4员工A122
5员工A222
6员工B132

常见低分回答

  1. “CTE 就是一个临时表”：这是最常见的混淆。CTE 的生命周期仅限于单个查询，而临时表（CREATE TEMPORARY TABLE）的生命周期是整个数据库会话（Session）。CTE 是逻辑概念，不一定物化成物理表；临时表则必定是物理存在的。
  2. “CTE 性能一定比子查询好”：不一定。对于简单的、非复用的场景，CTE 只是子查询的“语法糖”，数据库优化器可能会生成完全相同的执行计划。CTE 的性能优势主要体现在可复用性（避免重复计算）和优化器有时会选择物化（Materialize） CTE 结果以获得更优的执行计划。
  3. “WITH 后面可以跟多个 CTE，用逗号隔开，但它们是独立执行的”：这个说法不完全准确。后续的 CTE 可以引用在它之前定义的 CTE，它们之间存在依赖关系，并非完全独立。例如：WITH A AS (...), B AS (SELECT * FROM A)。
  4. 写错递归 CTE 的结构：例如，忘记 UNION ALL，或者在递归成员中忘记了终止条件（虽然上面的例子是隐式终止，但复杂场景需要显式终止条件），导致无限递归直至报错。

工程实践与优化

  • 物化（Materialization） vs. 内联（Inlining）：数据库优化器在处理 CTE 时有两种策略。

    • 内联：将 CTE 的定义直接代入主查询，类似于宏展开。对于简单、只被引用一次的 CTE，这是常见行为。
    • 物化：先计算 CTE 的结果，并将其存储在一个内部临时表（spool）中，然后在主查询中多次扫描这个临时表。当 CTE 被多次引用，或其计算成本较高时，物化通常更高效。
    • 如何观察：通过 EXPLAIN 查看执行计划。在 MySQL 中，物化的 CTE 会表现为一个 derived 表。在 PostgreSQL 中，会看到一个 CTE Scan 节点。
    • 手动控制：PostgreSQL 和 MySQL 8.0.21+ 提供了 MATERIALIZED 和 NOT MATERIALIZED 提示（Hint）来建议优化器采取何种策略。

  • 递归 CTE 的性能陷阱：

    • 深度与广度：递归深度过大或每层广度过宽，都可能导致指数级的计算量和内存消耗。
    • 安全阀：MySQL 提供了 cte_max_recursion_depth 系统变量（默认 1000）来防止无限递归耗尽服务器资源。在生产环境中，应根据业务场景评估并设置合理的递归深度。
    • 索引：递归成员中的 JOIN 条件列（如 e.manager_id 和 s.id）必须有索引，否则性能会急剧下降。

  • 选择 CTE、子查询还是临时表？

    • 子查询：用于最简单的、一次性的、非嵌套的逻辑。
    • CTE：用于拆解复杂逻辑、单条语句内结果复用、或递归查询。是提高代码可读性的首选。
    • 临时表：当中间结果集非常大，且需要在多个独立 SQL 语句之间共享时使用。临时表还可以创建索引，对超大数据量的中间结果进行优化。

常见追问与延伸题目

  1. 追问：CTE 和视图（View）有什么区别？

    • 回答要点 1 (生命周期与存储)：CTE 是临时的，只在当前查询中有效，其定义不被存储在数据库中。视图（View）是持久化的数据库对象，其定义被存储在数据字典中，可以被任何有权限的用户反复调用。
    • 回答要点 2 (使用场景)：CTE 用于简化单次复杂查询。视图用于封装常用逻辑、简化用户查询、或实现数据访问权限控制。
    • 回答要点 3 (性能)：标准视图（非物化视图）在每次被查询时，都会重新执行其内部的定义查询。而 CTE 在单次查询中如果被物化，则只计算一次。两者的性能差异取决于具体场景和优化器的决策。

  2. 追问：如果我有一个被引用了 5 次的 CTE，MySQL 优化器一定会物化它吗？

    • 回答要点 1 (不一定)：MySQL 的优化器是基于成本（Cost-Based Optimizer）的。它会估算“内联5次”的总成本和“物化1次+扫描5次”的总成本。如果 CTE 的结果集非常小（比如只有几行），那么内联的成本可能更低，因为它避免了物化写入和读取的 I/O 开销。
    • 回答要点 2 (如何确认)：最终是否物化，唯一的标准是查看 EXPLAIN 或 EXPLAIN ANALYZE 的输出。观察 Extra 列或执行树，看是否生成了派生表或临时表。
    • 回答要点 3 (版本差异)：在旧版本的 MySQL 8.0 中，优化器对 CTE 的处理相对保守。随着版本迭代，优化器变得越来越智能。了解自己生产环境的 MySQL 版本特性很重要。

  3. 追问：在 MySQL 5.7 中，我需要实现一个递归查询，该怎么办？

    • 回答要点 1 (应用层递归)：这是最直接的替代方案。在应用程序中（如 Java/Python/Go），先查询第一层数据，然后通过循环或递归函数，根据 manager_id 不断发起新的 SQL 查询来获取下一层数据，直到没有下属为止。缺点是多次网络往返，性能较差。
    • 回答要点 2 (自定义函数/存储过程)：可以在 MySQL 中编写一个存储过程，使用循环（LOOP）或游标（CURSOR）来模拟递归过程。将所有结果存入一个临时表，最后返回。这比应用层递归性能好，因为它在数据库服务器内部完成，但编写和维护起来更复杂。
    • 回答要点 3 (有限深度的 JOIN)：如果可以预知最大层级，比如最多只有 5 层，可以用多次 LEFT JOIN 来模拟。FROM emp e1 LEFT JOIN emp e2 ON e1.id = e2.manager_id LEFT JOIN emp e3 ON e2.id = e3.manager_id ...。这种方法非常笨拙，且有硬编码的深度限制，不推荐。最好的方案还是升级到 MySQL 8.0+。
← Previous
如何用窗口函数实现连续登录 N 天的用户检测？(row_number 与日期相减)
Next →
WITH RECURSIVE 的语法？给出查询某员工所有下属的递归 SQL。