如何使用 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中文網其他相關文章！