Làm việc với truy vấn đệ quy

Truy vấn phân cấp và đệ quy trong SQL Server

Jasmin Ludolf

Content Developer

Hệ phân cấp của một tổ chức CNTT

Tổ chức được mô tả bởi:

ID - Mã nhân viên
Name của nhân viên
JobTitle trong công ty
Department trong công ty
Supervisor trong công ty

Trường mô tả hệ phân cấp:

ID
Supervisor

Nhân viên 2 có Supervisor là 1

Tổ chức CNTT

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

Tác vụ thường gặp với dữ liệu phân cấp

Lấy hệ phân cấp của một bản ghi

Ai là người quản lý trực tiếp của bạn?

Lấy cấp bậc của hệ phân cấp

Lấy cấp bậc của một tổ chức

Gộp kết quả đệ quy vào một trường

Tôi có những quản lý nào?

Lấy hệ phân cấp

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

Lấy hệ phân cấp

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

Lấy cấp bậc trong hệ phân cấp

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

Lấy cấp bậc trong hệ phân cấp

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

Gộp kết quả đệ quy vào một trường

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

Gộp kết quả đệ quy vào một trường

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

Truy vấn tổ chức CNTT

Truy vấn phân cấp và đệ quy trong SQL Server