SQL中实现递归查询的操作方法

递归查询是SQL中处理层次结构数据的关键技术，本文详细介绍通过递归CTE实现多级数据遍历的具体方法。

数据表定义：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT  -- 指向上级，CEO的manager_id为NULL（或0，通常用NULL表示无上级）
);

初始化数据：

INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (101, '马总', NULL);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (201, '张工', 101);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (202, '王工', 101);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (301, '李工', 201);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (401, '赵工', 301);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (402, '刘工', 301);

查询需求：

需要获取id=101人员及其所有下级关系，同时标注每个成员的层级，起始层级设为0。

查询语句（MySQL 8.0+）：

使用递归CTE可以高效查询多级下属关系，以下SQL语句实现了层级标记功能：

WITH RECURSIVE subordinates AS (
    -- 初始查询：定位基准员工
    SELECT 
        id,
        name,
        manager_id, 
        0 AS level
    FROM employees
    WHERE id = 101
    UNION ALL
    -- 递归查询：逐级查找下属
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT *
FROM subordinates
ORDER BY level, id;

执行结果：

语法解释：

WITH RECURSIVE subordinates AS (...)是定义递归CTE的标准语法，其核心原理如下：

1.WITH关键字

1. 用于创建临时命名结果集，提升复杂查询的可读性。
2. CTE仅在当前查询中有效，类似临时视图。

2.RECURSIVE关键字

1. 声明CTE具有自引用特性，支持迭代查询。
2. MySQL必须显式声明，其他数据库可能允许省略。

3.subordinates命名规则

1. 自定义结果集名称，后续查询可直接引用。
2. 命名应体现数据特征，增强可读性。

4.递归结构组成

递归CTE包含两个关键部分：

(1)基础查询

SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 101

1. 首次执行获取初始数据。
2. 设置基准层级level=0。
3. 结果作为递归起点。

(2)递归查询

SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id

1. 通过自连接实现迭代查询。
2. 每次处理前次结果作为上级。
3. 层级自动递增。

(3)终止机制

1. 当迭代无新结果时自动终止。
2. 数据库默认设置递归深度限制。

5.结果输出

最终查询直接引用CTE名称获取完整结果集。

执行流程示例

假设数据结构如下：

1. 101管理201、202
2. 201管理301
3. 301管理401、402

执行过程分四个阶段：

1. 初始查询获取101记录
2. 首次递归获取201、202
3. 二次递归获取301
4. 三次递归获取401、402

典型应用场景

1. 组织架构管理
2. 产品BOM管理
3. 树形数据遍历
4. 网络路径分析

递归CTE提供了一种高效处理层次数据的标准方法，相比传统方案具有更好的可读性和性能表现。