是否可以将参数传递到CTE中,该CTE选择一个节点,然后选择其父节点,直到parentId
为null的根?
在下面的代码中,如果我传入一个参数,该参数选择Rain Coats,然后将其递归到男装,则parentId
为null,并选择该分支中的所有节点,包括子节点。有人可以帮我这个忙。我的示例只是递归并显示了深度
SQL范例:
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
最佳答案
您当前的CTE仅显示树中的所有项目及其Depth
和所有其他属性。因此,它工作正常。
要执行您想要的工作,您几乎必须“反转” CTE-首先获取您感兴趣的项目,作为CTE的“ anchor ”,然后“递归”到根目录:
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
这将完成您要查找的内容并输出:
Id ParentId Name Depth
11 10 Rain Coats 1
10 9 Jackets 2
9 8 Mens Clothing 3
8 NULL Mens Wear 4
更新
对于深度的相反顺序,可以使用以下命令:
;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
输出结果:
Id ParentId Name InverseDepth
11 10 Rain Coats 4
10 9 Jackets 3
9 8 Mens Clothing 2
8 NULL Mens Wear 1