SQL Hierarchie Díl 2 – Rekurzivní dotaz do parent-child hierarchie

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:

Rekurzivní dotazy - tabulka pro příklady

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é

Výsledek rekurzivního dotazu na hierarchii

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

Rekurzivní dotaz do hierarchie - ukázka na vrchního člena 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

 

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
SQL Hierarchie Díl 2 – Rekurzivní dotaz do parent-child hierarchie
Hodnocení

2 thoughts on “SQL Hierarchie Díl 2 – Rekurzivní dotaz do parent-child hierarchie

  1. Tomáš Novák

    Dobrý den,

    chtěl bych se zeptat, je zde možnost napsat dotaz, který by seskupil zaměstnance pod své nadřízené stejně jak to ukazujete ve 3 díle SQL Hierarchie? A pokud ano jakým způsobem by měl být napsán?

    Předem děkuji za odpověď

  2. Honza Zedníček Post author

    Ahoj, tady na webu si všichni tykáme

    Když potřebuješ zpracovat tuto tabulku sql scriptem, tak nejrychlejším a nejelegantnějším způsobem je využít rekurzivního dotazu jak zde popisuju – to co potřebuješ bys mohl najít ve 4. díle (https://biportal.cz/sql-hierarchie-dil-4-uroven-hierarchie-cesta-skript/). Je tam i obrázek jako výsledek (https://biportal.cz/sql-hierarchie-dil-4-uroven-hierarchie-cesta-skript/uroven-hierarchie-ukazka-prikladu/%5D.

    Tam je vidět, že ředitel je na prvním místě (hierarchy level = 0), manažeři na dalších ((hierarchy level = 1) a jejich podřízení nakonec (hierarchy level = 2). Dá se s tím ještě pohrát aby se docílilo třebas odsazení tak, aby ředitel neměl odsazení, manažeři měli odsazeni 3 mezery a jejich podřízení další 3 mezery. Udělal bys to tak, že se data seřadíš ORDER BY [Path_id_zamestnanec] a před jméno vložíš odsazení pomocí REPLICATE(‘ ‘,[Hierarchy_level]).

    Ať se daří!
    Honza

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

This site uses Akismet to reduce spam. Learn how your comment data is processed.