Comment une auto-jointure récursive dans SQL Server peut-elle être utilisée pour récupérer une hiérarchie d'employés ?

Auto-jointure récursive SQL Server pour la hiérarchie des employés

Une auto-jointure récursive dans SQL Server est une technique puissante pour naviguer dans les structures de données hiérarchiques. Illustrons cela à l'aide d'une table d'employés où chaque employé possède un identifiant unique et l'identifiant de son superviseur :

Exemple de tableau des employés :

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

Le défi :

Notre objectif est de récupérer la hiérarchie complète des employés sous un responsable spécifique (par exemple, John Doe). Une auto-jointure récursive fournit une solution élégante.

La solution :

Nous pouvons y parvenir en utilisant l'instruction WITH (Common Table Expression ou 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>

Cette requête commence par l'employé racine (pas de manager) et joint récursivement la table employees au CTE (EmployeeHierarchy), en élargissant la hiérarchie niveau par niveau jusqu'à ce que tous les subordonnés soient inclus.

Maintenir l'ordre hiérarchique :

Pour maintenir l'ordre hiérarchique correct, nous pouvons améliorer la requête :

<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>

Cette requête améliorée ajoute une colonne HierarchyPath, créant un chemin unique pour chaque employé, garantissant ainsi un tri hiérarchique approprié dans le résultat final. La fonction ROW_NUMBER() et la concaténation de chaînes maintiennent les relations parent-enfant.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!