Bekerja dengan kueri rekursif

Kueri Hierarkis dan Rekursif di SQL Server

Jasmin Ludolf

Content Developer

Hierarki organisasi TI

Organisasi dideskripsikan oleh:

ID - ID Karyawan
Name Karyawan
JobTitle di perusahaan
Department di perusahaan
Supervisor di perusahaan

Field yang mendeskripsikan hierarki:

ID
Supervisor

Karyawan 2 memiliki Supervisor 1

Organisasi TI

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

Tugas umum untuk data hierarkis

Ambil hierarki suatu record

Siapa atasan Anda?

Ambil level hierarki

Dapatkan level hierarki organisasi

Gabungkan hasil rekursi ke satu field

Saya punya atasan siapa saja?

Ambil hierarki

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

Ambil hierarki

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

Ambil level hierarki

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

Ambil level hierarki

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

Gabungkan hasil rekursi dalam satu field

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

Gabungkan hasil rekursi dalam satu field

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

Mari kueri organisasi TI

Kueri Hierarkis dan Rekursif di SQL Server