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

WITH RECURSIVE 的语法？给出查询某员工所有下属的递归 SQL。

What is the syntax for WITH RECURSIVE? Provide a recursive SQL query to find all

答案语言

考察要点

这道题面试官想看你是否掌握了现代 SQL 中处理层级或图结构数据的核心工具——公用表表达式（CTE），特别是其递归用法。考察点主要包括：

  1. SQL 标准与方言：是否了解 WITH RECURSIVE 是 SQL:1999 标准的一部分，以及在主流数据库（如 MySQL 8.0+, PostgreSQL, SQL Server）中的实现与语法差异。
  2. 递归逻辑：能否清晰地拆解递归查询的两个关键部分——初始集（Anchor Member）和递归集（Recursive Member）, 并理解其执行流程与终止条件。
  3. 数据建模：是否能将现实世界中的树状/图状关系（如组织架构、物料清单、社交网络）映射到数据库表中，并用 SQL 进行遍历。

核心概念

WITH RECURSIVE 是一种特殊的公用表表达式（CTE），它允许 CTE 在其自身的定义中引用自己，从而实现递归查询。这对于处理具有层级关系或图结构的数据至关重要。

其标准语法结构如下：

sql
 1WITH RECURSIVE cte_name (column_list) AS (
 2    -- 1. 初始集 (Anchor Member)
 3    -- 定义递归的起点，不引用 cte_name 自身
 4    SELECT ...
 5    FROM ...
 6    WHERE ...
 7

 8    UNION ALL
 9

10    -- 2. 递归集 (Recursive Member)
11    -- 引用 cte_name，定义如何从上一步的结果生成下一步的结果
12    SELECT ...
13    FROM table_name
14    JOIN cte_name ON (join_condition) -- 关键：与 CTE 自身连接
15    WHERE ...
16)
17-- 3. 最终查询
18-- 从构建好的 CTE 中查询最终结果
19SELECT * FROM cte_name;

核心三要素：

  1. 初始集 (Anchor Member)：这是递归的起点，是一个非递归的 SELECT 语句，用于生成第一批结果集。执行器首先执行此部分。
  2. 递归集 (Recursive Member)：这是递归的核心，它必须引用 cte_name 自身。执行器会重复执行此部分，将上一轮产生的结果作为输入，直到递归集不再产生任何新行为止。
  3. 终止条件：递归并非无限。当递归集的 SELECT 语句返回空结果集时，递归自动终止。这通常是通过 JOIN 条件或 WHERE 子句实现的。

数据示例：员工表 employees

假设我们有如下员工表，manager_id 指向其直属上级的 id。CEO 的 manager_id 为 NULL。

  • DDL 示例:

    sql
     1CREATE TABLE employees (
     2    id INT PRIMARY KEY,
     3    name VARCHAR(50),
     4    manager_id INT,
     5    INDEX idx_manager_id (manager_id) -- 关键索引
     6);
     7

     8INSERT INTO employees VALUES
     9(1, 'CEO', NULL),
    10(2, 'CTO', 1),
    11(3, 'CFO', 1),
    12(4, 'Tech Lead', 2),
    13(5, 'Backend Dev', 4),
    14(6, 'Frontend Dev', 4),
    15(7, 'Accountant', 3);

  • 层级关系图示 (ASCII):

    text
     1    1 (CEO)
     2    /     \
     3  2(CTO)   3(CFO)
     4   |         |
     5  4(Lead)    7(Accountant)
     6  /    \
     75(BE)  6(FE)

版本差异:

  • MySQL 8.0+ / PostgreSQL: 严格支持 WITH RECURSIVE 关键字。
  • SQL Server: 使用 WITH 关键字即可，无需 RECURSIVE。如果 CTE 内部引用了自身，SQL Server 会自动将其识别为递归 CTE。
  • MySQL 5.7 及更早版本: 不支持 CTE，更不支持递归查询。需要使用其他方法模拟。
  • Oracle: 与 SQL Server 类似，使用 WITH 即可，通过 START WITH ... CONNECT BY PRIOR 提供了另一种更简洁的层级查询语法。

多种解法

问题：查询 ID 为 2 (CTO) 的所有下属（直接和间接）。

解法 1：使用 WITH RECURSIVE (推荐)

这是最标准、最清晰的解法，适用于支持递归 CTE 的现代数据库。

  • 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server
  • SQL 代码：
    sql
     1WITH RECURSIVE subordinates AS (
     2    -- 初始集：找到 CTO 的直接下属
     3    SELECT id, name, manager_id, 1 AS level
     4    FROM employees
     5    WHERE manager_id = 2 -- 指定起始经理 ID
     6

     7    UNION ALL
     8

     9    -- 递归集：基于上一轮找到的下属，继续寻找他们的下属
    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 -- 关键：员工的经理ID = 上一轮找到的下属ID
    13)
    14SELECT id, name, manager_id, level
    15FROM subordinates;
  • 输出结果：
    idnamemanager_idlevel
    4Tech Lead21
    5Backend Dev42
    6Frontend Dev42
  • 复杂度：
    • 时间复杂度：O(V+E)O(V+E)，其中 V 是子树中的节点数（员工数），E 是边数（汇报关系数）。本质上是一次图的广度优先或深度优先遍历。
    • 空间复杂度：O(V)O(V)，需要存储中间和最终结果集。
  • 索引依赖：manager_id 列上的索引至关重要。递归集中的 JOIN subordinates s ON e.manager_id = s.id 会反复使用 manager_id 进行查找，无索引将导致全表扫描，性能急剧下降。

解法 2：使用存储过程和临时表（兼容老版本）

在 MySQL 5.7 等不支持递归 CTE 的版本中，可以通过程序化方式模拟递归。

  • 版本：✅ MySQL 5.x / 7.x
  • 思路：
    1. 创建一个临时表 sub_temp 存储所有下属 ID。
    2. 先将直接下属插入 sub_temp。
    3. 进入循环，每次查询 sub_temp 中上一轮新增的员工，找到他们的直接下属，再插入 sub_temp。
    4. 当某次循环不再有新员工加入时，循环结束。
  • SQL (存储过程示例)：
    sql
     1DELIMITER $$
     2CREATE PROCEDURE find_all_subordinates(IN root_id INT)
     3BEGIN
     4    -- 存储最终结果
     5    CREATE TEMPORARY TABLE IF NOT EXISTS result_subs (id INT, name VARCHAR(50), manager_id INT);
     6    TRUNCATE TABLE result_subs;
     7

     8    -- 存储每轮新发现的下属
     9    CREATE TEMPORARY TABLE IF NOT EXISTS temp_subs (id INT);
    10    TRUNCATE TABLE temp_subs;
    11

    12    -- 初始：找到直接下属
    13    INSERT INTO temp_subs (id) SELECT id FROM employees WHERE manager_id = root_id;
    14    INSERT INTO result_subs SELECT id, name, manager_id FROM employees WHERE manager_id = root_id;
    15

    16    WHILE (ROW_COUNT() > 0) DO
    17        -- 清空上一轮的临时ID，准备存放本轮新发现的下属的下属
    18        CREATE TEMPORARY TABLE IF NOT EXISTS next_level_subs (id INT);
    19        TRUNCATE TABLE next_level_subs;
    20

    21        -- 找到新一层的下属
    22        INSERT INTO next_level_subs (id)
    23        SELECT e.id FROM employees e JOIN temp_subs ts ON e.manager_id = ts.id;
    24

    25        -- 将新发现的下属存入最终结果集
    26        INSERT INTO result_subs
    27        SELECT e.id, e.name, e.manager_id FROM employees e JOIN next_level_subs nls ON e.id = nls.id;
    28

    29        -- 更新 temp_subs 为下一轮做准备
    30        TRUNCATE TABLE temp_subs;
    31        INSERT INTO temp_subs SELECT id FROM next_level_subs;
    32        DROP TEMPORARY TABLE next_level_subs;
    33    END WHILE;
    34

    35    SELECT * FROM result_subs;
    36    DROP TEMPORARY TABLE result_subs;
    37    DROP TEMPORARY TABLE temp_subs;
    38END $$
    39DELIMITER ;
    40

    41-- 调用
    42CALL find_all_subordinates(2);
  • 评价：这种方法将声明式的 SQL 查询变成了过程式的代码，逻辑复杂、可读性差，且性能通常不如原生的递归 CTE。只应在不支持递归 CTE 的老旧环境中使用。

常见低分回答

  1. 忘记 UNION ALL 使用 UNION：UNION 会对结果集进行去重，在递归查询中，这不仅带来不必要的性能开销（排序或哈希），还可能在某些场景下（如一个节点可从多条路径到达）导致递归提前终止。UNION ALL 是标准实践。
  2. 递归部分逻辑错误：JOIN 条件写反，如 JOIN subordinates s ON e.id = s.manager_id，这会导致逻辑混乱，无法正确向下遍历。
  3. 对终止条件理解不清：认为需要手动写 WHERE level < 10 之类的终止条件。实际上，递归的隐式终止条件是“递归集不再产生新行”。手动限制深度是用于防止无限循环或控制查询范围，而非必须。
  4. 在 MySQL 5.7 中强行写 WITH RECURSIVE：暴露了对数据库版本特性不了解的短板。面试官会认为候选人缺乏实际生产经验。
  5. 无限循环风险：如果数据中存在循环引用（例如，A 是 B 的经理，B 也是 A 的经理），标准的递归查询会陷入无限循环，直到达到数据库的递归深度限制（如 MySQL 的 cte_max_recursion_depth）。高分回答应能指出此风险。

工程实践与优化

  • 防止无限循环：在生产环境中，数据可能不干净。为防止循环引用导致的无限递归，可以在 CTE 中增加一个路径（path）追踪列。
    sql
     1WITH RECURSIVE subordinates AS (
     2    SELECT id, name, manager_id, CAST(id AS CHAR(200)) AS path
     3    FROM employees WHERE id = 2 -- 从经理本人开始，方便路径追踪
     4

     5    UNION ALL
     6

     7    SELECT e.id, e.name, e.manager_id, CONCAT(s.path, ',', e.id)
     8    FROM employees e JOIN subordinates s ON e.manager_id = s.id
     9    WHERE NOT FIND_IN_SET(e.id, s.path) -- 关键：如果下一个节点已在路径中，则停止
    10)
    11SELECT * FROM subordinates WHERE id != 2; -- 排除经理本人
  • 递归深度限制：MySQL 中有 cte_max_recursion_depth 系统变量（默认 1000），用于防止失控的递归耗尽服务器资源。如果业务上需要更深的递归，可以调整会话级别的变量：SET SESSION cte_max_recursion_depth = 10000;。
  • 索引优化：manager_id 列的 BTREE 索引是递归查询性能的生命线。EXPLAIN 分析递归查询时，递归部分通常显示为 derived 或 materialized，关键是看 employees 表的访问类型是否为 ref，并且 key 是否用上了 idx_manager_id。
  • 替代数据模型：对于读频繁、写不频繁的超深或超大层级结构，每次都用递归 CTE 查询可能性能不佳。可以考虑在设计表时就优化：
    • 物化路径 (Materialized Path)：将每个节点的完整上级路径（如 '1/2/4/'）存为一个字段。查询所有下属就变成了一个 LIKE '...%' 查询，非常高效。
    • 闭包表 (Closure Table)：用一张独立的表存储所有节点之间的关系（包括间接关系）及其深度。查询任何节点的下属只需在该表上进行一次简单的 SELECT。

常见追问与延伸题目

  1. 追问：如果数据中存在循环引用（A 是 B 的经理，B 也是 A 的经理），你刚才的 SQL 会发生什么？如何改进？

    • 回答要点：标准的递归 CTE 会陷入无限循环，直到达到 cte_max_recursion_depth 限制而报错。改进方法是在递归过程中记录并检查访问路径，如上文“工程实践与优化”中所示，通过增加一个 path 列，并在递归集的 WHERE 子句中检查下一个节点是否已经存在于 path 中（使用 FIND_IN_SET 或类似函数），如果存在，则说明出现了环，应终止该路径的递归。

  2. 追问：除了查询组织架构，WITH RECURSIVE 还有哪些典型的应用场景？

    • 回答要点：
      • 物料清单 (Bill of Materials, BOM)：查询一个产品需要的所有子零件、子零件的子零件等。
      • 图遍历/路径查找：如查询社交网络中两个用户之间的所有可能关系链，或查找航班网络中的所有可达城市。
      • 生成序列：可以用来生成连续的数字、日期或时间序列，用于报表填充或数据生成。例如，生成从 1 到 100 的数字序列。
      • 字符串或 JSON 解析：在某些数据库中，可以用来递归地拆分和处理复杂的字符串或 JSON 结构。

  3. 追问：如果这张员工表有千万级数据，层级很深，一个实时 API 需要查询某个高层领导的所有下属，你还会直接用这条 SQL 吗？为什么？有什么更好的方案？

    • 回答要点：不会直接用。因为对于大数据量和深层级，实时递归查询的性能开销会很大，响应时间可能达到秒级甚至更长，无法满足实时 API 的低延迟要求。
    • 更好的方案：
      • 预计算/物化视图：采用上文提到的物化路径或闭包表模型。这些模型在写入数据时（如新增/修改员工汇报关系）会产生额外开销来维护层级结构，但查询时极为高效，只需简单的 SELECT 和 LIKE 或 JOIN，可以将查询时间降到毫秒级。
      • 缓存：对于不常变动的组织架构，可以将高频查询的结果（如各部门负责人的下属列表）缓存在 Redis 等内存数据库中，设置合理的过期时间。
      • 离线计算：如果允许数据有一定延迟，可以通过定时任务（如每晚）运行递归查询，将结果存储在一张“展平”的结果表中，API 直接查询这张结果表。
← Previous
什么是 CTE（Common Table Expression）？相比子查询有何优势？
Next →
如何用递归 CTE 生成日期序列、补齐某段时间的空缺？