Werken met recursieve queries

Hiërarchische en recursieve queries in SQL Server

Jasmin Ludolf

Content Developer

De hiërarchie van een IT-organisatie

De organisatie wordt beschreven door:

ID - Medewerker-ID
Name van medewerker
JobTitle in het bedrijf
Department in het bedrijf
Supervisor in het bedrijf

Velden die de hiërarchie beschrijven:

ID
Supervisor

Medewerker 2 heeft Supervisor 1

De IT-organisatie

+-----------------------+---------------------+------------+------------+
|ID | Name              | Position            | Department | Supervisor |    
|-----------------------|---------------------|------------|------------|
| 1 | Heinz Griesser    | IT Director         | IT         | 0          |
| 2 | Andreas Sitter    | Security Manager    | IT         | 1          |
| 3 | Thomas Bergman    | Innovation Manager  | IT         | 1          |
| 4 | Hannes Berg       | Operation Manager   | IT         | 1          |
| 5 | Anna Kruggel      | Administrator       | IT         | 4          |
| 6 | Karin Pacher      | Developer           | IT         | 4          |
+-----------------------+---------------------+------------+------------+

Veelvoorkomende taken voor hiërarchische data

De hiërarchie van een record ophalen

Wie is je supervisor?

Het hiërarchieniveau ophalen

Bepaal het hiërarchieniveau van een organisatie

Recursieresultaten samenvoegen in één veld

Welke supervisors heb ik?

De hiërarchie ophalen

WITH hierarchy AS (
    SELECT ID,Supervisor
        FROM employee 
        WHERE supervisor = 0

    UNION ALL
    SELECT emp.ID, emp.Supervisor
        FROM employee emp

    JOIN hierarchy
      ON emp.Supervisor = hierarchy.ID)
SELECT *
    FROM hierarchy

De hiërarchie ophalen

+---+-----------+
|ID |Supervisor |
|---|-----------|
|1  | 0         |
|2  | 1         |
|3  | 2         |
+---+-----------+

Het niveau van de hiërarchie ophalen

WITH hierarchy AS (
    SELECT ID, Supervisor, 1 as LEVEL
        FROM employee
           WHERE Supervisor = 0

    UNION ALL
    SELECT emp.ID, emp.Supervisor, LEVEL + 1
        FROM employee emp

    JOIN hierarchy
    ON emp.Supervisor = hierarchy.ID
)
SELECT *
    FROM hierarchy

Het niveau van de hiërarchie ophalen

+---+-----------+-------|
|ID |Supervisor | Level |
|---|-----------|-------|
|1  | 0         | 0     |
|2  | 1         | 1     |
+---+-----------+-------+

Recursieresultaten samenvoegen in één veld

WITH hierarchy AS (
    SELECT ID, Supervisor, CAST('0' AS VARCHAR(MAX)) as PATH
        FROM employee
        WHERE Supervisor = 0

    UNION ALL
    SELECT emp.ID, emp.Supervisor, Path + '->' + CAST(emp.Supervisor AS VARCHAR(MAX))
        FROM employee emp
    INNER JOIN hierarchy
    ON emp.Supervisor = hierarchy.ID
)
SELECT *
    FROM hierarchy

Recursieresultaten samenvoegen in één veld

+-----+--------+
| PATH         |
|--------------|
| 0 -> 1 -> 4  |
+-----+--------+

Laten we de IT-organisatie bevragen

Hiërarchische en recursieve queries in SQL Server