SQL Server CTE select single tree branch structure upto the root
Is it possible to pass a parameter into a CTE that selects a node then selects its parent up to the root where the parentId is null?
In my code below if I pass in a parameter that selects Rain Coats and then recurses up the tree to mens wear where its parentId is null and selects all the nodes in that branch including children. Could someone help me with this please. My example just recurses and shows the depth
SQL example:
DECLARE @Department TABLE
(
Id INT NOT NULL,
Name varchar(50) NOT NULL,
ParentId int NULL
)
INSERT INTO @Department SELECT 1, 'Toys', null
INSERT INTO @Department SELECT 2, 'Computers', null
INSERT INTO @Department SELECT 3, 'Consoles', 2
INSERT INTO @Department SELECT 4, 'PlayStation 3', 3
INSERT INTO @Department SELECT 5, 'Xbox 360', 2
INSERT INTO @Department SELECT 6, 'Games', 1
INSERT INTO @Department SELECT 7, 'Puzzles', 6
INSERT INTO @Department SELECT 8, 'Mens Wear', null
INSERT INTO @Department SELECT 9, 'Mens Clothing', 8
INSERT INTO @Department SELECT 10, 'Jackets', 9
INSERT INTO @Department SELECT 11, 'Rain Coats', 10
;WITH c
AS
(
SELECT Id, Name,1 AS Depth
FROM @Department
WHERE ParentId is null
UNION ALL
SELECT t.Id, t.Name, c.Depth + 1 AS 'Level'
FROM @Department T
JOIN c ON t.ParentId = c.Id
)
SELECT * FROM c WHERE c.Id = 3
Your current CTE just shows all the items in the tree, with their Depth and all the other properties. As such, it works fine.
To do what you're looking for, you have to almost "invert" the CTE - grab the item you're interested in first, as the "anchor" of your CTE, and then "recurse up" to the root:
DECLARE @StartID INT = 11
;WITH c
AS
(
SELECT Id, ParentId, Name, 1 AS Depth
FROM @Department
WHERE Id = @startID
UNION ALL
SELECT t.Id, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
FROM @Department T
INNER JOIN c ON t.Id = c.ParentId
)
SELECT *
FROM c
This will do what you're looking for and output:
Id ParentId Name Depth
11 10 Rain Coats 1
10 9 Jackets 2
9 8 Mens Clothing 3
8 NULL Mens Wear 4
Update
For a reverse order of Depth you can use this:
;WITH c
AS
(
SELECT Id, ParentId, Name, 1 AS Depth
FROM @Department
WHERE Id = @startID
UNION ALL
SELECT t.Id, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
FROM @Department T
INNER JOIN c ON t.Id = c.ParentId
)
SELECT Id,
ParentID,
Name,
MAX(Depth) OVER() - Depth + 1 AS InverseDepth
FROM c
Output from this:
Id ParentId Name InverseDepth
11 10 Rain Coats 4
10 9 Jackets 3
9 8 Mens Clothing 2
8 NULL Mens Wear 1
Currently, your CTE has the root as its anchor, and goes from parent to child in its recursive part. If you want the whole tree, you'll need to start at the child of interest, and work your way up. This is one way to do that. I have introduced a new column StartingId which remains constant as we walk up the tree - this is what we will select based on:
;WITH c
AS
(
SELECT Id AS StartingId, Id, ParentId, Name, 0 AS Height
FROM @Department
UNION ALL
SELECT c.StartingId, p.Id, p.ParentId, p.Name, c.Height + 1 AS Height
FROM @Department p INNER JOIN c ON p.Id = c.ParentId
)
SELECT * FROM c WHERE c.StartingId = 11
gives
StartingId Id ParentId Name Height
----------- ----------- ----------- ----------------------------------------------
11 11 10 Rain Coats 0
11 10 9 Jackets 1
11 9 8 Mens Clothing 2
11 8 NULL Mens Wear 3
上一篇: 使用SQL CTE表来包含路径和所有子项