SQL 实战—递归 SQL：层级结构查询与处理树形数据 - sdk社区

在数据库中，树形或层级结构的数据非常常见，如组织架构、分类目录、评论回复等。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

id	name	manager_id	position
1	Alice	NULL	CEO
2	Bob	1	CTO
3	Carol	1	CFO
4	David	2	Engineer
5	Eve	2	Engineer
6	Frank	3	Accountant
7	Grace	4	Intern

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. 查询结果

id	name	manager_id	position	level	path
1	Alice	NULL	CEO	1	Alice
2	Bob	1	CTO	2	Alice -> Bob
3	Carol	1	CFO	2	Alice -> Carol
4	David	2	Engineer	3	Alice -> Bob -> David
5	Eve	2	Engineer	3	Alice -> Bob -> Eve
6	Frank	3	Accountant	3	Alice -> Carol -> Frank
7	Grace	4	Intern	4	Alice -> Bob -> David -> Grace

三、逐步剖析递归 SQL 执行过程

第 1 步：非递归部分执行，查找根节点（Alice，CEO）。
第 2 步：递归查找下属，即 manager_id 为 Alice 的员工（Bob 和 Carol）。
第 3 步：继续递归，查找 Bob 和 Carol 的下属（David、Eve、Frank）。
第 4 步：直到没有下属，递归结束。

四、案例 2：分类目录的层级查询

1. 数据表结构 categories

id	category_name	parent_id
1	Electronics	NULL
2	Mobile Phones	1
3	Laptops	1
4	iPhone	2
5	Samsung	2
6	Dell	3
7	MacBook	3

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. 查询结果

id	category_name	parent_id	level	path
1	Electronics	NULL	1	Electronics
2	Mobile Phones	1	2	Electronics -> Mobile Phones
3	Laptops	1	2	Electronics -> Laptops
4	iPhone	2	3	Electronics -> Mobile Phones -> iPhone
5	Samsung	2	3	Electronics -> Mobile Phones -> Samsung
6	Dell	3	3	Electronics -> Laptops -> Dell
7	MacBook	3	3	Electronics -> 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 语法，可以高效地遍历父子关系，实现路径计算和深度查询。
在大数据量或深层递归场景中，需要配合索引和查询优化技术，确保递归查询的效率。