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

2023-12-14 16:28:56

假设我们有一个表格 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 Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        CONCAT(rc.HierarchyPath, ' > ', e.Name),
        rc.Level + 1
    FROM Employees e
    INNER 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 Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        e.Department,
        CONCAT(rc.HierarchyPath, ' > ', e.Name),
        rc.Level + 1
    FROM Employees e
    INNER 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

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

文章来源:https://blog.csdn.net/sunsijia21983/article/details/134995353
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。