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

Poslední aktualizace:

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

 

SQL Hierarchie Díl 2 – Rekurzivní dotaz do parent-child hierarchie
5 (100%) 1 vote[s]

Honza Zedníček

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích společnosti Kentico, s.r.o v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval v několika bankách na pozicích finanční controller a manažer. Po práci se měním na vášnivého hráče tenisu, šachu a ochutnávače dobrých rumů. Mým velké štěstím, koníčkem a někdy stresorem se před 3 lety stal syn Kubíček. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Přidejte si mě na LinkedIn nebo se subscribněte na RSS kanál

Rubrika: SQL příkazy Užitečné SQL skripty Štítky: ,

About Honza Zedníček

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích společnosti Kentico, s.r.o v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval v několika bankách na pozicích finanční controller a manažer. Po práci se měním na vášnivého hráče tenisu, šachu a ochutnávače dobrých rumů. Mým velké štěstím, koníčkem a někdy stresorem se před 3 lety stal syn Kubíček. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Přidejte si mě na LinkedIn nebo se subscribněte na RSS kanál

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.

Tato stránka používá Akismet k omezení spamu. Podívejte se, jak vaše data z komentářů zpracováváme..