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 中国語 Web サイトの他の関連記事を参照してください。