递归 CTE

什么是递归 CTE

递归 CTE 让 SQL 能处理层级结构（组织架构、目录树）和路径遍历（路线搜索、依赖分析）。普通 SQL 做不到"不确定深度的递归展开"，递归 CTE 填补了这个空白。

语法

WITH RECURSIVE cte_name AS (
    -- 锚定部分（Anchor）：起点，只执行一次
    SELECT ... FROM table WHERE ...

    UNION ALL

    -- 递归部分（Recursive）：引用自身，逐层展开
    SELECT ... FROM table
    JOIN cte_name ON ...
    WHERE ...  -- 终止条件
)
SELECT * FROM cte_name;

两个关键部分：

• 锚定部分 — 定义递归的起点（比如根节点、起始城市）
• 递归部分 — 引用 CTE 自身，每轮用上一轮新产生的行作为输入

执行机制

递归 CTE 的执行过程类似 BFS（广度优先）：

1. 运行锚定部分，得到初始结果集
2. 把初始结果放入"工作表"
3. 用工作表作为输入运行递归部分
4. 新产生的行替换工作表，同时追加到最终结果
5. 重复 3-4，直到某轮没有新行产出，递归终止

组织架构遍历

找 Alice 的所有下属（包括下属的下属）：

WITH RECURSIVE subordinates AS (
    -- 起点：Alice 本人
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE emp_name = 'Alice'

    UNION ALL

    -- 每轮找出上一轮结果的直接下属
    SELECT e.emp_id, e.emp_name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT * FROM subordinates;

level 列记录层级深度，方便后续分析。

防止循环

如果数据有环（A 的上级是 B，B 的上级又是 A），递归会死循环。常用防环方法是路径字符串检测：

WITH RECURSIVE path_cte AS (
    SELECT from_city, to_city,
        from_city || '->' || to_city AS path
    FROM flights
    WHERE from_city = 'A'

    UNION ALL

    SELECT p.from_city, f.to_city,
        p.path || '->' || f.to_city
    FROM path_cte p
    JOIN flights f ON p.to_city = f.from_city
    WHERE p.path NOT LIKE '%' || f.to_city || '%'
)
SELECT * FROM path_cte;

把已经走过的城市拼成路径字符串，每次递归前检查目标城市是否已在路径中。

序列生成

递归 CTE 也可以用来生成辅助序列，在补全日期缺失、构造测试数据时很实用：

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

练习

WITH RECURSIVE cat_path AS (
  SELECT id, name, parent_id, name AS full_path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
    cp.full_path || ' > ' || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, name, full_path FROM cat_path ORDER BY full_path;

WITH RECURSIVE route AS (
  SELECT from_city, to_city,
    from_city || '->' || to_city AS path,
    distance_km AS total_dist
  FROM flights
  WHERE from_city = 'A'

  UNION ALL

  SELECT r.from_city, f.to_city,
    r.path || '->' || f.to_city,
    r.total_dist + f.distance_km
  FROM route r
  JOIN flights f ON r.to_city = f.from_city
  WHERE r.path NOT LIKE '%' || f.to_city || '%'
)
SELECT path, total_dist
FROM route
WHERE to_city = 'D'
ORDER BY total_dist;