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

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

1. 限制递归深度：防止死循环或层级过深导致查询缓慢。

   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;

2. 索引优化：对关联字段（如 parent_id）建立索引，提升递归查询速度。

3. 提前处理：定期构建物化视图，减少递归查询的频率。

---

六、总结

• 递归 SQL 是处理树形和层级数据的有力工具，能简化复杂的层级查询任务。
• 通过 WITH RECURSIVE 语法，可以高效地遍历父子关系，实现路径计算和深度查询。
• 在大数据量或深层递归场景中，需要配合索引和查询优化技术，确保递归查询的效率。