Skip to the content

Recursief zoeken in SQL Server (CTE) common table expression

Soms heb je tabellen waarbij de waarde uit een rij een kind kan zijn van een andere rij in dezelfde tabel. In mijn voorbeeld gebruik ik een prijslijst die onderdeel kan zijn van een bovenliggende prijslijst.

Als ik nu alle tarieven van de onderliggende prijslijsten van 1 enkel artikel wil wijzigen, op basis van de bovenliggende prijs, dan moet ik dus steeds op zoek naar een volgende onderliggende prijslijst. Voor dit zogenaamde recursief doorlopen van rijen heeft SQL s+Server een mooie oplossing.

Deze recursieve oplossing heet een common table expression (CTE) wat eigenlijk niets meer inhoudt dan het tijdelijk opslaan van gegevens in een gemeenschappelijke tabel.

Recursief voorbeeld


De prijs in de prijslijst 1 gaat met 10% omhoog.

De prijs in prijslijst 2 is gelijk aan 90% van de prijs in prijslijst 1.

De prijs in prijslijst 3 is gelijk aan 90% van de prijs in prijslijst 2.

Deze lastige bewerking kunnen we nu als volgt uitvoeren met een CTE.

 

--Tijdelijke tabel prijslijsten
DECLARE @DEMOTABLE1 AS TABLE
	(
	tarPriceListId INT NOT NULL,
	tarPriceListName VARCHAR(80) NOT NULL,
	tarParentListId INT NULL
	)

INSERT INTO @DEMOTABLE1(tarPriceListId, tarPriceListName, tarParentListId)
SELECT 1, 'Prijslijst -1-', NULL UNION
SELECT 2, 'Prijslijst -2-', 1 UNION
SELECT 3, 'Prijslijst -3-', 2

--Tijdelijke tabel tarieven
DECLARE @DEMOTABLE2 AS TABLE
	(
		tarItemPriceId INT NOT NULL,
		tarItemCode	VARCHAR(16) NOT NULL,
		tarPriceListId INT NOT NULL,
		tarItemPrice NUMERIC(28,10) DEFAULT(0) NOT NULL
	)
	
INSERT INTO @DEMOTABLE2(tarItemPriceId, tarItemCode, tarPriceListId, tarItemPrice)
SELECT 1, 'UREN', 1, 50.00 UNION
SELECT 2, 'UREN', 2, 47.50 UNION
SELECT 3, 'UREN', 3, 45.00;


WITH PriceRecursive(RowNo, Pricelist, ParentPriceList, ItemCode, Price, NewPrice, Level)
	AS
	(
		SELECT 
			RowNo = ROW_NUMBER() Over(Order By (Select 1)),
			A.tarPriceListId,
			A.tarParentListId,
			B.tarItemCode,
			B.tarItemPrice,
			CAST(B.tarItemPrice * 1.10 AS NUMERIC(28,10)),
			0 AS LEVEL
		FROM @DEMOTABLE1 A OUTER APPLY (SELECT * FROM @DEMOTABLE2 B WHERE A.tarPriceListId = B.tarPriceListId) B
		WHERE A.tarParentListId IS NULL
		UNION ALL
		SELECT
			RowNo + 1,
			A.tarPriceListId,
			A.tarParentListId,
			B.tarItemCode,
			B.tarItemPrice,
			CAST(C.NewPrice * 0.90 AS NUMERIC(28,10)),
			LEVEL+1
		FROM @DEMOTABLE1 A OUTER APPLY (SELECT * FROM @DEMOTABLE2 B WHERE A.tarPriceListId = B.tarPriceListId) B
		INNER JOIN PriceRecursive C ON C.Pricelist = A.tarParentListId
	)

SELECT * FROM PriceRecursive;

SELECT * FROM @DEMOTABLE1
SELECT * FROM @DEMOTABLE2

Recursief CTE uitgelegd


De veldnamen in de CTE nemen het veldtype aan van de eerste query die wordt aangeroepen. Zonder expliciete conversie naar een numeriek veldtype kan er in de onderliggende select statements niet worden gerekend.

WITH PriceRecursive(RowNo, Pricelist, ParentPriceList, ItemCode, Price, NewPrice, Level)

In het eerste select statement bepalen we het hoogste niveau van de recursieve zoekopdracht. In de WHERE stellen we dan ook dat er geen bovenliggende prijslijst mag zijn.

WHERE A.tarParentListId IS NULL

In het volgende select statement zie je dat we een join maken tussen de prijslijst en de bovenliggende regel door de CTE op te nemen in de query.

INNER JOIN PriceRecursive C ON C.Pricelist = A.tarParentListId

Reinder.eu | join cte sql server

 

In de eerste regel geven we de prijsverhoging van 10% aan en in alle volgende regels bepalen we dat de prijs 90% is van de daarbovenliggende prijslijst prijs.

CAST(C.NewPrice * 0.90 AS NUMERIC(28,10)),

 

Reinder.eu | recursief zoeken cte sql server