递归 CTE：处理层级数据

什么是递归 CTE

递归 CTE 引用自身，适合处理层级数据（组织结构、目录树、图遍历）。

基本结构：

WITH RECURSIVE cte_name AS (
    -- 基础查询（锚点）
    SELECT ... WHERE base_condition
    
    UNION ALL
    
    -- 递归查询（引用 cte_name）
    SELECT ... FROM cte_name WHERE recursive_condition
)
SELECT * FROM cte_name;

第一个递归 CTE：生成数列

WITH RECURSIVE numbers AS (
    -- 锚点：起始值
    SELECT 1 AS n
    
    UNION ALL
    
    -- 递归：n + 1
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

结果：

 n
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10

工作原理：

1. 执行锚点查询：SELECT 1 → 结果 {1}
2. 执行递归查询：SELECT 1 + 1 → 结果 {2}
3. 继续递归：SELECT 2 + 1 → 结果 {3}
4. ...直到条件不满足（n < 10）

组织结构示例

员工表（manager_id 指向上级）：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'Alice', NULL),    -- CEO
    (2, 'Bob', 1),
    (3, 'Charlie', 1),
    (4, 'Diana', 2),
    (5, 'Eve', 2),
    (6, 'Frank', 3);

查询所有下属

WITH RECURSIVE subordinates AS (
    -- 锚点：从 Alice 开始
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1
    
    UNION ALL
    
    -- 递归：查找下级
    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;

结果：

 id |  name   | manager_id | level
----+---------+------------+-------
  1 | Alice   |            |     1
  2 | Bob     |          1 |     2
  3 | Charlie |          1 |     2
  4 | Diana   |          2 |     3
  5 | Eve     |          2 |     3
  6 | Frank   |          3 |     3

查询完整路径

WITH RECURSIVE org_path AS (
    SELECT id, name, manager_id, name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, op.path || ' > ' || e.name
    FROM employees e
    JOIN org_path op ON e.manager_id = op.id
)
SELECT name, path FROM org_path;

结果：

  name   |          path
---------+-------------------------
 Alice   | Alice
 Bob     | Alice > Bob
 Charlie | Alice > Charlie
 Diana   | Alice > Bob > Diana
 Eve     | Alice > Bob > Eve
 Frank   | Alice > Charlie > Frank

实际应用

应用 1：目录树

WITH RECURSIVE folder_tree AS (
    SELECT id, name, parent_id, name AS path, 0 AS depth
    FROM folders
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT f.id, f.name, f.parent_id, ft.path || '/' || f.name, ft.depth + 1
    FROM folders f
    JOIN folder_tree ft ON f.parent_id = ft.id
)
SELECT REPEAT('  ', depth) || name AS indented_name, path
FROM folder_tree
ORDER BY path;

应用 2：图遍历（查找连接）

WITH RECURSIVE connections AS (
    -- 起点
    SELECT user_id, friend_id, 1 AS distance
    FROM friendships
    WHERE user_id = 1
    
    UNION
    
    -- 朋友的朋友（去重用 UNION 而非 UNION ALL）
    SELECT c.user_id, f.friend_id, c.distance + 1
    FROM connections c
    JOIN friendships f ON c.friend_id = f.user_id
    WHERE c.distance < 3  -- 限制深度
)
SELECT DISTINCT friend_id, MIN(distance) AS degrees_of_separation
FROM connections
GROUP BY friend_id;

应用 3：账户余额追踪

WITH RECURSIVE balance AS (
    -- 初始余额
    SELECT 0 AS transaction_num, 0 AS balance
    
    UNION ALL
    
    SELECT 
        t.id,
        b.balance + t.amount
    FROM transactions t
    JOIN balance b ON t.id = b.transaction_num + 1
)
SELECT * FROM balance;

终止条件

递归必须有终止条件，否则无限循环。

方式 1：WHERE 条件

SELECT n + 1 FROM numbers WHERE n < 10  -- n >= 10 时停止

方式 2：没有更多行

SELECT e.id FROM employees e
JOIN subordinates s ON e.manager_id = s.id
-- 当没有下级时自动停止

方式 3：深度限制

SELECT ..., s.level + 1
FROM ... JOIN subordinates s ...
WHERE s.level < 5  -- 限制深度

循环检测

如果数据有循环引用（如 A → B → A），递归会无限循环。

防止循环：

WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, ARRAY[id] AS path
    FROM employees
    WHERE id = 1
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, h.path || e.id
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
    WHERE NOT (e.id = ANY(h.path))  -- 检测循环
)
SELECT * FROM hierarchy;

性能考虑

• 递归 CTE 可能很慢（大数据集或深层次）
• 使用 UNION（去重）代替 UNION ALL 避免重复行
• 限制递归深度
• 考虑添加索引到递归连接的列

PostgreSQL vs MySQL

PostgreSQL：完整支持递归 CTE

MySQL 8.0+：支持递归 CTE，语法相同，但有一些限制：

• 不支持 CYCLE 子句（需要手动检测循环）
• 递归深度限制为 1000（可配置）

MySQL < 8.0：不支持递归 CTE，需要用存储过程或应用层处理。

练习

1. 生成序列：生成 1 到 100 的数列。

2. 组织结构：查询某个员工的所有下属（所有层级）。

3. 路径追踪：显示从 CEO 到每个员工的完整路径。

4. 查找上级：反向递归，查询某个员工的所有上级。

5. 深度限制：只查询 3 层以内的下属。

6. 计数统计：计算每个经理的直接和间接下属总数。

7. 循环检测：在有潜在循环的数据中使用路径数组检测循环。

8. 实际场景：构建产品类别树，显示缩进的层级结构。

完成练习后，打开本章答案页核对。

本章小结

第四章学习了 SQL 高级查询：

• 窗口函数：OVER、PARTITION BY、ORDER BY
• 排名函数：ROW_NUMBER、RANK、DENSE_RANK
• 窗口聚合：移动平均、累计求和、LAG/LEAD
• CTE：WITH 子句，提高可读性
• 递归 CTE：处理层级数据和图遍历

这些是高级数据分析的核心工具。第五章将学习事务和并发控制。

回到第四章概览 · 返回SQL 学习路线