在数据库中，树形或层级结构的数据非常常见，如组织架构、分类目录、评论回复等。SQL 提供了递归查询的能力，通过递归通用表表达式（CTE），可以高效地查询和处理树形数据。本文将通过实际案例详细讲解递归 SQL 的应用。
一、递归 SQL 基本概念
1. 什么是递归 SQL？
递归 SQL 是指在 SQL 查询中，自我引用以遍历层级结构或处理递归关系的一种方式。 MySQL 8.0、PostgreSQL 和 SQL Server 等数据库均支持递归查询。
2. 递归 CTE 基本语法
WITH RECURSIVE cte_name (column1, column2, ...) AS (
-- 非递归部分（初始查询）
初始查询
UNION ALL
-- 递归部分（递归查询）
递归查询（引用 cte_name 本身）
)
SELECT * FROM cte_name;
解释：
非递归部分：查询递归的起点（通常是树的根节点）。递归部分：查询每一层的子节点或下级关系。UNION ALL：将递归结果逐层叠加，直到递归终止。
二、实战案例：组织架构树
1. 数据表结构 employees
idnamemanager_idposition1AliceNULLCEO2Bob1CTO3Carol1CFO4David2Engineer5Eve2Engineer6Frank3Accountant7Grace4Intern
2. 需求：查询整个组织架构树（层级显示）
目标：获取员工的层级关系，显示路径和层级深度。
3. SQL 实现：递归 CTE 查询
WITH RECURSIVE org_tree AS (
-- 非递归部分（根节点）
SELECT
id,
name,
manager_id,
position,
1 AS level,
CAST(name AS CHAR(255)) AS path
FROM employees
WHERE manager_id IS NULL -- 根节点，即 CEO
UNION ALL
-- 递归部分
SELECT
e.id,
e.name,
e.manager_id,
e.position,
t.level + 1 AS level,
CONCAT(t.path, ' -> ', e.name) AS path
FROM employees e
JOIN org_tree t
ON e.manager_id = t.id
)
SELECT * FROM org_tree
ORDER BY level, manager_id;
4. 查询结果
idnamemanager_idpositionlevelpath1AliceNULLCEO1Alice2Bob1CTO2Alice -> Bob3Carol1CFO2Alice -> Carol4David2Engineer3Alice -> Bob -> David5Eve2Engineer3Alice -> Bob -> Eve6Frank3Accountant3Alice -> Carol -> Frank7Grace4Intern4Alice -> Bob -> David -> Grace
三、逐步剖析递归 SQL 执行过程
第 1 步：非递归部分执行，查找根节点（Alice，CEO）。第 2 步：递归查找下属，即 manager_id 为 Alice 的员工（Bob 和 Carol）。第 3 步：继续递归，查找 Bob 和 Carol 的下属（David、Eve、Frank）。第 4 步：直到没有下属，递归结束。
四、案例 2：分类目录的层级查询
1. 数据表结构 categories
idcategory_nameparent_id1ElectronicsNULL2Mobile Phones13Laptops14iPhone25Samsung26Dell37MacBook3
2. 查询分类目录树（层级展示）
WITH RECURSIVE category_tree AS (
SELECT
id,
category_name,
parent_id,
1 AS level,
CAST(category_name AS CHAR(255)) AS path
FROM categories
WHERE parent_id IS NULL -- 顶级分类
UNION ALL
SELECT
c.id,
c.category_name,
c.parent_id,
t.level + 1 AS level,
CONCAT(t.path, ' -> ', c.category_name) AS path
FROM categories c
JOIN category_tree t
ON c.parent_id = t.id
)
SELECT * FROM category_tree
ORDER BY level, parent_id;
3. 查询结果
idcategory_nameparent_idlevelpath1ElectronicsNULL1Electronics2Mobile Phones12Electronics -> Mobile Phones3Laptops12Electronics -> Laptops4iPhone23Electronics -> Mobile Phones -> iPhone5Samsung23Electronics -> Mobile Phones -> Samsung6Dell33Electronics -> Laptops -> Dell7MacBook33Electronics -> Laptops -> MacBook
五、应用场景与优化建议
1. 应用场景
组织架构树：查询上下级关系。分类目录：电商商品分类，处理嵌套分类树。评论系统：层级评论回复，构建嵌套评论。权限管理：递归遍历角色与权限关系。
2. 优化建议
限制递归深度：防止死循环或层级过深导致查询缓慢。
WITH RECURSIVE category_tree AS (
SELECT id, category_name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.category_name, c.parent_id, t.level + 1
FROM categories c
JOIN category_tree t ON c.parent_id = t.id
WHERE t.level < 5 -- 限制递归深度为 5
)
SELECT * FROM category_tree;
索引优化：对关联字段（如 parent_id）建立索引，提升递归查询速度。提前处理：定期构建物化视图，减少递归查询的频率。
六、总结
递归 SQL 是处理树形和层级数据的有力工具，能简化复杂的层级查询任务。通过 WITH RECURSIVE 语法，可以高效地遍历父子关系，实现路径计算和深度查询。在大数据量或深层递归场景中，需要配合索引和查询优化技术，确保递归查询的效率。