-- Using CTEs Recursively
use demo
go
CREATE TABLE CarParts
(
CarID int NOT NULL,
Part varchar(15),
SubPart varchar(15),
Qty int
)
GO
INSERT CarParts
VALUES (1, 'Body', 'Door', 4)
INSERT CarParts
VALUES (1, 'Body', 'Trunk Lid', 1)
INSERT CarParts
VALUES (1, 'Body', 'Car Hood', 1)
INSERT CarParts
VALUES (1, 'Door', 'Handle', 1)
INSERT CarParts
VALUES (1, 'Door', 'Lock', 1)
INSERT CarParts
VALUES (1, 'Door', 'Window', 1)
INSERT CarParts
VALUES (1, 'Body', 'Rivets', 1000)
INSERT CarParts
VALUES (1, 'Door', 'Rivets', 100)
INSERT CarParts
VALUES (1, 'Door', 'Mirror', 1)
go
select * from CarParts
go
WITH CarPartsCTE(SubPart, Qty)
AS
(
-- Anchor Member (AM):
-- SELECT query that doesn’t refer back to CarPartsCTE
SELECT SubPart, Qty
FROM CarParts
WHERE Part = 'Body'
UNION ALL
-- Recursive Member (RM):
-- SELECT query that refers back to CarPartsCTE
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
FROM CarPartsCTE
INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
WHERE CarParts.CarID = 1
)
--outer query
SELECT SubPart, SUM(Qty) AS TotalNUM
FROM CarPartsCTE
GROUP BY SubPart
|
