Někdy potřebujeme v SQL vytvořit tabulku, která obsahuje hierarchii. Hierarchie je struktura (někdy se označuje jako parent-child hierarchie), která obsahuje závislosti mezi jednotlivými členy. Můžeme si to představit jako organizaci ve firmě. Firma má zaměstnance a ti mají svého nadřízeného. Nadřízení zaměstnanci mají opět svého nadřízeného a takto můžeme pokračovat až k CEO firmy, který už žádného nadřízeného nemá.

Hierarchie – Jak vytvořit správně parent-child strukturu v SQL tabulce?

Představme si tabulku se zaměstnanci. Hierarchie se vytváří v rámci 1 tabulky formou tzv. self-reference. To znamená, každého zaměstnance máme definovaného primárním klíčem (id_zamestnanec) a v dalším sloupci (id_nadrizeny) budeme mít cizí klíč, který povede na primární klíč id_zamestnanec.

Příklad: Vytvoříme tabulku pro firmu, která má celkem 15 zaměstnanců z toho jsou 4 manažeři a 1 ředitel firmy.

Nachystáme si pro hierarchii tabulku “zamestnanci”

Mezi jednotlivými zaměstnanci definujeme vztah nadřízenosti a podřízenosti pomocí pole id_nadrizeny:

CREATE TABLE Temp.dbo.zamestnanci (
  id_zamestnanec INT PRIMARY KEY,
  id_nadrizeny INT,
  pozice VARCHAR(255),
  jmenoprijmeni VARCHAR(255),
  plat MONEY
)

INSERT INTO Temp.dbo.zamestnanci (
  id_zamestnanec,
  id_nadrizeny,
  pozice,
  jmenoprijmeni,
  plat
)
VALUES
  (1,11,'zamestnanec A','Jan Novák',20000),
  (2,11,'zamestnanec B', 'Petra Černá',23000),
  (3,11,'zamestnanec C', 'Petr Holomek',21000),
  (4,12,'zamestnanec D', 'František Netáhlo',25000),
  (5,12,'zamestnanec E', 'Vlastimil Táhlo',19000),
  (6,12,'zamestnanec F', 'Aleš Nedělám',27000),
  (7,13,'zamestnanec G', 'Zbyšek Pohoda',24000),
  (8,13,'zamestnanec H', 'Daniela Zuřivá',22000),
  (9,14,'zamestnanec I', 'Lenka Malá',21500),
  (10,14,'zamestnanec J', 'Pavel Držgrešle',29000),
  (11,15,'manager 1', 'Milan Flákač',35000),
  (12,15,'manager 2', 'Martin Nedoma',40000),
  (13,15,'manager 3', 'Robert Robertek',31000),
  (14,15,'manager 4', 'Miloš Novák',50000),
  (15,NULL,'ředitel firmy', 'Jan Přísný',90000)

Přidáme cizí klíč

Přidáme Selfreferenci na pole id_nadrizeny:

ALTER TABLE [dbo].[zamestnanci] WITH CHECK ADD CONSTRAINT [FK_zamestnanci_zamestnanci] FOREIGN KEY([id_nadrizeny])
REFERENCES [dbo].[zamestnanci] ([id_zamestnanec])

Výsledek testovací tabulky a selfreference

Ve výsledku máme takovou hezkou tabulku obsahující cizí klíč sama do sebe (selfreferenci):

Vytvoření hierarchie - ukázka

V některém z dalších článků připomenu, jak se na parent-child strukturu dotazovat skripty pomocí rekurzivních dotazů a jak se vypořádat s hierarchiemi při reportování přes Reporting Services a Power BI.

4.7/5 - (3 votes)

Jan Zedníček - Data & Finance

Jmenuji se Honza Zedníček a působím jako freelancer. Tuto práci dělám pro různé firmy již přes 10 let. Před tím jsem dlouhou dobu pracoval také jako BI developer, finanční controller, analytik a krátce jako finanční manažer. Vše pro společnosti z oblasti IT, bankovnictví a výroby. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti a názory se snažím už několik let zapisovat na tento web, aby sloužily i někomu dalšímu - zejména studentům a zájemcům o informace z oblastí Business intelligence, korporátních financí a reportingu.

=> Přihlašte se do naší Excel facebook skupiny (1.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »
=> Pokud vám článek pomohl, sdílejte ho nebo mě uveďte na vašich webových stránkách jako užitečný zdroj informací.

Leave a Reply

Your email address will not be published. Required fields are marked *