środa, 11 marca 2015

Hierarchie i CTE

Do napisania tego posta zainspirował mnie temat na forum wss.geekclub.pl. Czasami zachodzi konieczność przechowywania w bazie danych hierarchi. Najprostszym przykładem tego typu danych może byc lista pracowników wraz z ich wzajemnymi powiązaniami.

Dzięki wykorzystaniu rekursywnych CTE w bardzo prosty sposób można stworzyć zapytanie, które zwróci wyniki w kolejności hierarchi organizacyjnej.

Załóżmy, że mamy następującą tabelę.

EmployeeId JobTitle ManagerId
15 General Manager 16
16 CEO NULL
25 Development Manager 15
10 Team Leader 25
11 Software Developer 10
declare @employees table (

    EmployeeId int,

    JobTitle varchar(255),

    ManagerId int

)

 

insert into @employees values 

(15,'General Manager', 16), (16, 'CEO', null), (25,'Development Manager',15), 

(10, 'Team Leader', 25), (11, 'Software Developer', 10)


Numery identyfikacyjne celowo są w losowej kolejności i nie są one bezpośrednio powiązne z hierarchią organizacyjną.
Założmy, że potrzebujemy pełną strukturę ogranizacyjna w kolejności poczynając od najważniejszej osoby w organizacji.

EmployeeIdJobTitle
16CEO
15General Manager
25Development Manager
10Team Leader
11Software Developer

Powyzszy problem może być rozwiązany za pomocą następującego zapytania


;with hierarchy as

(

select EmployeeId, JobTitle, 1 as level from @employees where ManagerId is null

 

union all

 

select e.EmployeeId, e.JobTitle, level + 1 from @employees e

inner join hierarchy e2 on e.ManagerId = e2.EmployeeId

)

 

select EmployeeId, JobTitle From  hierarchy order by level asc


Założmy teraz, że jesteśmy zainteresowani tylko szefem działu wraz ze wszystkimi osobami które do niego raportują.
EmployeeIdJobTitle
25Development Manager
10Team Leader
11Software Developer

Aby osiągnąć powyższy wynik wystarczy zmodyfikować predykat w pierwszym zapytaniu.


;with hierarchy as

(

select EmployeeId, JobTitle, 1 as level from @employees where EmployeeId = 25

 

union all

 

select e.EmployeeId, e.JobTitle, level + 1 from @employees e

inner join hierarchy e2 on e.ManagerId = e2.EmployeeId

)

 

select EmployeeId, JobTitle From  hierarchy order by level asc


Podsumowując, za pomocą CTE można w bardzo łatwy sposób napisać zapytania, które będą zaróno eleganckie jak i skuteczne.


Prześlij komentarz