SQL Server CTE 选择单树分支结构直至根

Question

是否可以将参数传递到 CTE，该 CTE 选择一个节点，然后选择其父节点，直至 parentId 为空的根？

在下面的代码中，如果我传入一个选择雨衣的参数，然后将树递归到男装，其中 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

Answer 1

您当前的 CTE 仅显示树中的所有项目及其深度 和所有其他属性。因此，它工作得很好。

为了做你正在寻找的事情，你几乎必须“反转”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