是否可以将参数传递到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

10-07 19:30
查看更多