当前位置：首页 > news >正文

SQL Server ，使用递归查询具有层级关系的数据。

news 来源：原创 2024/5/13 9:19:23

假设我们有一个表格 Employees，其中包含员工的层级关系信息，每一行包括员工的ID、姓名以及上级员工的ID。

下面是一个示例表格及其数据：

Employees
----------------------
EmployeeID | Name   | ManagerID
----------------------
1          | Alice  | NULL
2          | Bob    | 1
3          | Carol  | 1
4          | Dave   | 2
5          | Eve    | 2
6          | Frank  | 3

要以横向展示的方式查询递归数据，你可以使用如下的 SQL 查询：

WITH RecursiveCTE AS (SELECT EmployeeID,Name,ManagerID,CAST(Name AS VARCHAR(MAX)) AS HierarchyPath,1 AS LevelFROM EmployeesWHERE ManagerID IS NULLUNION ALLSELECT e.EmployeeID,e.Name,e.ManagerID,CONCAT(rc.HierarchyPath, ' > ', e.Name),rc.Level + 1FROM Employees eINNER JOIN RecursiveCTE rc ON e.ManagerID = rc.EmployeeID
)
SELECT EmployeeID,HierarchyPath
FROM RecursiveCTE
ORDER BY EmployeeID;

在这个查询中，我们使用了一个递归的 CTE 来构建层级路径。在递归部分，我们连接 Employees 表与递归CTE，将每个员工的姓名添加到其上级的路径之后，并增加级别。最终，我们从递归CTE中选择员工ID和横向展示的层级路径。

结果将类似于这样：

EmployeeID | HierarchyPath
---------------------------
1          | Alice
2          | Alice > Bob
3          | Alice > Carol
4          | Alice > Bob > Dave
5          | Alice > Bob > Eve
6          | Alice > Carol > Frank

要在递归查询的横向展示中添加新字段，你可以在递归CTE中包含需要的额外字段，并在每个递归步骤中更新这些字段。下面的示例演示了如何添加一个新字段 Department 到递归查询结果中：

假设我们扩展了之前的示例表格 Employees，使其包含部门信息：

Employees
----------------------
EmployeeID | Name   | ManagerID | Department
----------------------
1          | Alice  | NULL      | HR
2          | Bob    | 1         | IT
3          | Carol  | 1         | HR
4          | Dave   | 2         | IT
5          | Eve    | 2         | IT
6          | Frank  | 3         | HR

现在，我们要在横向展示中包含每个员工的部门信息。以下是查询示例：

WITH RecursiveCTE AS (SELECT EmployeeID,Name,ManagerID,Department,CAST(Name AS VARCHAR(MAX)) AS HierarchyPath,1 AS LevelFROM EmployeesWHERE ManagerID IS NULLUNION ALLSELECT e.EmployeeID,e.Name,e.ManagerID,e.Department,CONCAT(rc.HierarchyPath, ' > ', e.Name),rc.Level + 1FROM Employees eINNER JOIN RecursiveCTE rc ON e.ManagerID = rc.EmployeeID
)
SELECT EmployeeID,HierarchyPath,Department
FROM RecursiveCTE
ORDER BY EmployeeID;

在这个查询中，我们在递归CTE中包含了 Department 字段，并在递归的第二部分中将每个员工的部门信息传递下去。最终的查询结果将包括每个员工的ID、横向展示的层级路径以及部门信息：

EmployeeID | HierarchyPath           | Department
------------------------------------------------
1          | Alice                   | HR
2          | Alice > Bob             | IT
3          | Alice > Carol           | HR
4          | Alice > Bob > Dave      | IT
5          | Alice > Bob > Eve       | IT
6          | Alice > Carol > Frank   | HR

这个查询结果在横向展示的同时还包含了部门信息。你可以根据需要继续添加其他字段。