Er zijn een aantal scenario’s te bedenken waarin het runnen van een recursieve query uitkomst kan bieden. Dit voorbeeld gaat in op een recursieve query binnen een afdelingstabel waardoor een organogram opgebouwd kan worden. Hierbij wordt gebruik gemaakt van een Common Table Expression (CTE).
Binnen de afdelingstabel in dit voorbeeld is er sprake van een zogenaamde parent. Deze kolom doet aangeven dat er binnen de tabel afdeling sprake is van een zelfreferentie.
{highslide type=”img” url=”artikelen/tsql/recursieve_query_met_cte/common_table_expression_00.jpg” width=400 captionText=’CTE’}{/highslide}
De afdeling met afdelingID 2 heeft als parent afdeling de afdeling met afdelingID 1 en ga zo maar door. Op basis van deze tabel wil ik aangeven wat de relatie tussen de verschillende afdelingen onderling is, m.a.w. welke afdeling heeft welke afdeling onder zich. Door gebruik te maken van een Common Table Expression (CTE) met een UNION kan deze vraag eenvoudig worden beantwoord.
CTE
Een CTE is niets anders dan een expressie dat een tijdelijke resultset teruggeeft waar meerdere malen naar gerefereerd kan worden. Omdat deze resultset meerdere malen benaderd kan worden is dit een uitgelezen middel om een recursieve query mee te maken.
De syntax voor een CTE is vrij eenvoudig:
{geshi xml:lang=”tsql” lines=”false”}
WITH expression_name [ ( column_name [,…n] ) ]
AS
( CTE_query_definition )
SELECT
FROM expression_name{/geshi}
De WITH clausule geeft de start van de opbouw van de CTE. Na de WITH wordt de naam van de CTE opgegeven en vervolgens de namen van de kolommen. Na het definiëren van de kolommen komt de AS en vervolgens de query die de CTE opbouwt.
Na de definitie van de CTE volgt een SELECT statement om de data vanuit de CTE op te halen.
Onderstaand voorbeeld is een CTE op basis van de AdventureWorks database.
{geshi xml:lang=”tsql” lines=”false”}
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT
SalesPersonID,
COUNT(*),
MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT
E.EmployeeID,
OS.NumberOfOrders,
OS.MaxDate,
E.ManagerID,
OM.NumberOfOrders,
OM.MaxDate
FROM HumanResources.Employee AS E
INNER JOIN Sales_CTE AS OS ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID
GO{/geshi}
De CTE geeft een resultset terug waarin het totaal aantal orders met de meest recente orderdate per salesperson wordt teruggegeven. Vervolgens wordt er twee maal naar de opgebouwde CTE gerefereerd in de tweede SELECT statement . Een maal om per medewerker het aantal orders met de meest recente datum op te halen en een tweede maal om het zelfde te doen maar dan voor de managers.
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
———– ————– ———- ——— ————– ———-
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01
Recursieve Query
Op basis van de Afdeling tabel uit bovenstaand voorbeeld moet worden vastgesteld welke afdeling onder welke afdeling valt om zo een organogram op te bouwen.
Allereerst een statement om de CTE op te bouwen:
{geshi xml:lang=”tsql” lines=”false”}
WITH AfdelingTree (AfdelingID, ParentAfdelingID, Omschrijving, AfdelingsLevel, SortKey)
AS
(
SELECT
AfdelingID, ParentAfdelingID, Omschrijving, 0, cast(AfdelingID AS VARBINARY(900))
FROM Afdeling
WHERE ParentAfdelingID IS NULL
UNION ALL
SELECT
a.AfdelingID, a.ParentAfdelingID, a.Omschrijving, b.AfdelingsLevel+1,
CAST(b.SortKey + CAST (a.AfdelingID AS BINARY(4)) AS VARBINARY(900))
FROM Afdeling as a
INNER JOIN AfdelingTree as B on a.ParentAfdelingID = b.AfdelingID
) {/geshi}
De CTE heeft de naam AfdelingTree gekregen waarin een aantal kolommen worden gedefinieerd.
Vervolgens wordt het SELECT statement opgebouwd om de eerste afdeling te definiëren waar de ParentAfdelingID NULL is. Dit is de afdeling bovenin de organogram en definieert het hoogste niveau. Hierbij word een sortkey gedefinieerd welke later gehanteerd wordt voor een ORDER BY.
In het tweede SELECT statement gaan we op zoek naar de child-afdelingen die de ParentAfdelingID van de voorgaande query bevatten. Dit wordt gedaan door een JOIN van de CTE in het tweede statement, de zelfreferentie. Dit gedeelte van de query wordt herhaald totdat er geen records meer over blijven, kortom tot alle afdelingen zijn behandeld en de parent-child organogram is opgesteld. Hiernaast wordt de sortkey toegevoegd aan de sortkey van de parent om zo een nivellering te krijgen.
Nu de CTE is opgebouwd rest alleen nog een SELECT statement om de opgestelde organogram in de CTE op te halen.
{geshi xml:lang=”tsql” lines=”false”}
SELECT
REPLICATE(‘–> ‘, AfdelingsLevel) + ‘(‘ + (CAST(AfdelingID AS VARCHAR(10))) + ‘) ‘+ Omschrijving AS AfdelingOmschrijving,
AfdelingID
ParentAfdelingID,
Omschrijving,
AfdelingsLevel,
SortKey
FROM AfdelingTree
ORDER BY SortKey{/geshi}
Door gebruik te maken van de REPLICATE functie kan er per afdelinglevel worden ingesprongen om zo de hiërarchie in de organogram binnen SQL Server zichtbaar te maken. De output van de query heeft het volgende resultaat als gevolg:
{highslide type=”img” url=”artikelen/tsql/recursieve_query_met_cte/common_table_expression_01.jpg” width=600 captionText=’CTE’}{/highslide}