sql server 遍历下级

在 SQL Server 中遍历下级节点（如树形结构数据），可通过 递归公共表表达式（CTE） 实现。以下是具体步骤和示例：

---

步骤说明

1. 表结构示例
   假设表名为 Departments，包含部门ID和父部门ID：

   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       ParentDepartmentID INT,
       DepartmentName VARCHAR(50)
   );
   

2. 递归CTE语法
   使用 WITH 子句定义递归CTE，包含锚定成员（初始查询）和递归成员（关联下级）。

3. 查询逻辑

   • 锚定成员：选择起始节点（如根节点）。
   • 递归成员：通过 JOIN 关联自身CTE，逐层查找下级。

---

完整示例代码

-- 创建示例数据
INSERT INTO Departments VALUES
(1, NULL, '总部'),
(2, 1, '技术部'),
(3, 2, '开发组'),
(4, 2, '测试组'),
(5, 1, '市场部');

-- 递归查询所有下级（包含自身）
WITH DepartmentCTE AS (
    SELECT 
        DepartmentID, 
        ParentDepartmentID, 
        DepartmentName,
        0 AS Level  -- 层级从0开始
    FROM Departments
    WHERE DepartmentID = 1  -- 指定根节点

    UNION ALL

    SELECT 
        d.DepartmentID, 
        d.ParentDepartmentID, 
        d.DepartmentName,
        cte.Level + 1  -- 每层深度+1
    FROM Departments d
    INNER JOIN DepartmentCTE cte 
        ON d.ParentDepartmentID = cte.DepartmentID
)
SELECT 
    DepartmentID,
    DepartmentName,
    ParentDepartmentID,
    Level
FROM DepartmentCTE
ORDER BY Level, DepartmentID;

---

结果示例

DepartmentID	DepartmentName	ParentDepartmentID	Level
1	总部	NULL	0
2	技术部	1	1
5	市场部	1	1
3	开发组	2	2
4	测试组	2	2

---

关键参数说明

• 锚定成员：WHERE DepartmentID = 1 指定从根节点（总部）开始遍历。
• 递归成员：通过 INNER JOIN DepartmentCTE 逐层关联下级。
• Level：层级字段表示节点深度（根节点为0，子节点递增）。

---

扩展场景

1. 仅查询直接下级
   去除递归部分，直接筛选父节点：

   SELECT * 
   FROM Departments
   WHERE ParentDepartmentID = 1;  -- 获取总部的直接下级
   

2. 限制递归深度
   使用 OPTION (MAXRECURSION n) 控制最大递归层数（默认100）：

   WITH DepartmentCTE AS (...)
   SELECT ...
   OPTION (MAXRECURSION 10);  -- 最多递归10层
   

3. 排除根节点
   修改锚定成员为直接下级：

   WITH DepartmentCTE AS (
       SELECT DepartmentID, ParentDepartmentID, DepartmentName, 1 AS Level
       FROM Departments
       WHERE ParentDepartmentID = 1  -- 从总部的直接下级开始
       UNION ALL
       ...
   )
   

---

通过递归CTE，可高效遍历树形结构数据，适用于部门层级、分类目录等场景。