V minulém článku jsem popisoval, jak správně založit do sql tabulky hierarchii se zaměstnanci (viz. článek Jak správně vytvořit parent-child strukturu). Nyní bude řeč o tom, jak se do hierarchií dotazovat a jak s nimi pracovat. Můžete na to jít bláznivými skripty nebo od lesa. Způsob od lesa zahrnuje rekurzivní dotaz.

Rekurzivní dotaz na hierarchie s využitím Common table Expression v SQL Server

Pro ukázky využijeme tabulku z minulého článku, která obsahuje zaměstnance a jejich nadřízené, skripty k založení tabulky naleznete v článku zde:

Pomocí rekurzivního dotazu s využitím CTE (Common table Expression) si necháme vypsat všechny zaměstnance jejichž nadřízeným je manažer 3 (id_zamestnanec=13), jak na to?

WITH nadrizeny AS
(
  --vyber nadzizeneho
  SELECT id_zamestnanec, id_nadrizeny, pozice, jmenoprijmeni,plat
  FROM Temp.dbo.zamestnanci
  WHERE id_zamestnanec=13
    UNION ALL
  --rekurzivní dotaz na jeho podřízené
  SELECT zam.id_zamestnanec, zam.id_nadrizeny, zam.pozice, zam.jmenoprijmeni, zam.plat
  FROM Temp.dbo.zamestnanci zam INNER JOIN nadrizeny nad
           ON zam.id_nadrizeny = nad.id_zamestnanec
)
SELECT * FROM nadrizeny;

Výsledkem jsou 3 záznamy – 2 podřízení zaměstnanci a sám manažer. Okem si můžeme z výchozí tabulky zkontrolovat, že manager 3 má skutečně 2 podřízené

Pokud vybereme do rekurze ředitele s id_zamestnanec=15, tak výsledkem je všech 15 zaměstnanců (ředitel je nejvyšší člen hierarchie)

Jak pracuje rekurzivní dotaz

Rekurzivní dotaz je definován uvnitř CTE prostřednictvím UNION ALL. V druhé části je JOIN do sebe sama

1. Založíme CTE s aliasem nadrizeny
2. Provedeme inicializaci v rámci první části union all s výběřem nějakého zaměstnance z tabulky zamestnanci => anchor
3. V druhé části provedeme join tabulky zamestnanci s CTE nadrizeni a tím vyvoláme rekurzi přes všechny stupně hierarchie
4. Výsledkem je seznam podřízených daného zaměstnance včetně jeho samotného