SQL CTE递归：处理树形与层次数据

使用公用表表达式（CTE）实现递归查询，轻松处理层级关系数据 · 难度：入门 · +15XP

什么是递归CTE？

公用表表达式（CTE）是SQL中定义临时结果集的一种方式。递归CTE能够引用自身，特别适合处理树形结构数据，如组织架构、分类目录、评论回复等。

语法结构

WITH RECURSIVE cte_name AS (
  -- 锚点查询（初始结果）
  SELECT ... FROM table WHERE condition
  UNION ALL
  -- 递归查询（引用自身）
  SELECT ... FROM cte_name JOIN table ON ...
)
SELECT * FROM cte_name;

注意：必须包含两个部分，并用UNION ALL连接。递归部分必须引用CTE自身，且最终需要终止条件（通常通过WHERE或JOIN条件）。

实战案例：组织架构树

假设员工表包含id, name, manager_id（上级ID）。

WITH RECURSIVE org_tree AS (
  -- 锚点：找到最高领导（没有上级的人）
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归：找到每个员工的下属
  SELECT e.id, e.name, e.manager_id, t.level + 1
  FROM employees e
  INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, id;

生成数字序列

递归CTE也可以用于生成连续的数字或日期：

WITH RECURSIVE number_series AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM number_series WHERE num < 10
)
SELECT * FROM number_series;

性能与限制

大多数数据库默认递归深度限制为100或1000层（如SQLite默认1000，MySQL默认1000）。可通过设置参数调整，但应避免无限递归。

练习提示

• 使用递归CTE查询某个员工的所有下属（包含间接下属）
• 生成从当前日期开始未来30天的日期序列
• 在论坛评论表中，找出某条评论的所有回复链