Tomáš Havetta

Název tohoto článečku je trošku nejasný, ale osvětlení přijde hned.

Dělám na projektu, kde je v SQL 2008 databázi tabulka obsahující objekt X. Ten může mít klasické hierarchické uspořádání s jiným objektem typu X, čím se vytvoří typický strom. Každý objekt X má jednu vlastnost M, která je daná číselníkem C.

Úkol pro řešení byl na první pohled jednoduchý. Mám jednu instanci X1 objektu typu X, která ale může být v libovolné úrovni stromu. Jak co nejjednodušeji zjistím, které hodnoty číselníku C nejsou použité v žádném objektu typu X, který je ve stejném stromě jak ten X1.

Samozřejmě by se dalo to řešit kódem, ale po krátkém bádání ve schopnostech SQL 2005/8 jsem našel dle mého názoru elegantní řešení, které vyřeší vše v SQL serveru. Využil jsem schpnost rekurzivního prohledávání v SQL bez nutnosti vytvářet temporary table. Odborně to má název CTE, je plno blogů které popisují prohledávání od root elementu směrem dolů (většinou vazbu zaměstnanec - vedoucí)

Já udělal při bádání dvě tabulky, Task se sloupečkem Id, ParentId, Popis a Typ. Pole Typ je id číselníku Type (druhá tabulka). První blok mého SQL kódu hledá root prvek stromu (to jest ten, kde parentId bude NULL) a pak lehce najde vše, co se ve stromě vyskytuje a pomocí NOT IN najde v číselníku jen to, co ve stromě není.

Jen pro zajímavost, zda se ve stromě pohybuji směrem dolů nebo nahoru je dáno pomocí JOIN podmínky v sekcích WITH, podle toho zda z tabulky beru Id nebo ParentId.  

DECLARE @root AS INT;

DECLARE @start AS INT;

SET @start = 5;

WITH UpCTE

AS

(

      SELECT Id, ParentID, Popis,Typ, 0 as lvl

      FROM Task

      WHERE Id = @start

      UNION ALL

      SELECT C.Id, C.ParentID, C.Popis,C.Typ, P.lvl + 1

      FROM UpCTE as P

      JOIN Task AS C ON P.ParentID = C.Id

)

SELECT @root =Id FROM UpCTE

WHERE ParentID IS NULL;

WITH SubsCTE

AS

(

      SELECT Id, ParentID, Popis,Typ, 0 as lvl

      FROM Task

      WHERE Id = @root

      UNION ALL

      SELECT C.Id, C.ParentID, C.Popis,C.Typ, P.lvl + 1

      FROM SubsCTE as P

      JOIN Task AS C ON C.ParentID = P.Id

)

SELECT * FROM Type

WHERE Id NOT IN (SELECT Typ FROM SubsCTE)