如何使用 SQL Server 中的递归自连接来检索员工层次结构？

员工层次结构的 SQL Server 递归自连接

SQL Server 中的递归自连接是一种用于导航分层数据结构的强大技术。 让我们使用员工表来说明这一点，其中每个员工都有唯一的 ID 及其主管的 ID：

员工表示例：

<code>| EmployeeID | Name       | ManagerID |
|------------|------------|------------|
| 1          | John Doe   | NULL       |
| 2          | Jane Smith | 1          |
| 3          | Mary Jones | 2          |
| 4          | Bob Johnson| 3          |</code>

挑战：

我们的目标是检索特定经理（例如 John Doe）下的完整员工层次结构。 递归自连接提供了一个优雅的解决方案。

解决方案：

我们可以使用 WITH 语句（公用表表达式或 CTE）来实现此目的：

<code class="language-sql">WITH EmployeeHierarchy AS (
  SELECT
    EmployeeID,
    Name,
    ManagerID
  FROM
    employees
  WHERE
    ManagerID IS NULL  -- Start with the top-level employee (no manager)
  UNION ALL
  SELECT
    e.EmployeeID,
    e.Name,
    e.ManagerID
  FROM
    employees e
  JOIN
    EmployeeHierarchy q
    ON e.ManagerID = q.EmployeeID  -- Recursively join to itself
)
SELECT
  *
FROM
  EmployeeHierarchy;</code>

此查询从根员工（无经理）开始，并递归地将 employees 表连接到 CTE (EmployeeHierarchy)，逐级扩展层次结构，直到包含所有下级。

维持层级顺序：

为了保持正确的层次顺序，我们可以增强查询：

<code class="language-sql">WITH EmployeeHierarchy AS (
  SELECT
    EmployeeID,
    Name,
    ManagerID,
    CAST(ROW_NUMBER() OVER (ORDER BY EmployeeID) AS VARCHAR(MAX)) AS HierarchyPath
  FROM
    employees
  WHERE
    ManagerID IS NULL
  UNION ALL
  SELECT
    e.EmployeeID,
    e.Name,
    e.ManagerID,
    q.HierarchyPath + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY e.ManagerID ORDER BY e.EmployeeID) AS VARCHAR(MAX))
  FROM
    employees e
  JOIN
    EmployeeHierarchy q
    ON e.ManagerID = q.EmployeeID
)
SELECT
  *
FROM
  EmployeeHierarchy
ORDER BY
  HierarchyPath;</code>

此改进的查询添加了 HierarchyPath 列，为每个员工创建唯一的路径，确保最终输出中正确的分层排序。 ROW_NUMBER() 函数和字符串连接维持父子关系。

以上是如何使用 SQL Server 中的递归自连接来检索员工层次结构？的详细内容。更多信息请关注PHP中文网其他相关文章！