sql - 递归计算后代数量

Question

我有一个带有导航的表，它使用 ParentId 重新连接它自己。我试图计算每条记录有多少后代，我知道我需要在递归中增加一个计数器，我只是不知道该怎么做!

任何帮助将不胜感激!

CREATE TABLE [dbo].[Navigation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AnchorText] [varchar](50) NOT NULL,
    [ParentID] [int] NULL)

insert into Navigation
select 'Level 1', null
union
select 'Level 2', 1
union
select 'Level 3', 2
union
select 'Level 4', 3


WITH NavigationCTE (Id, AnchorText, ParentId, NumberofDescendants) as
(
      Select Id, AnchorText, ParentId, 'Number of Descendants Here' as NumberofDescendants
      from dbo.Navigation nav

      union ALL 

      select nav.Id, nav.AnchorText, nav.ParentId,  'Number of Descendants Here' as NumberofDescendants
      from dbo.Navigation nav

      join Navigation ON nav.ParentId = nav.Id
)


SELECT * FROM NavigationCTE

编辑 在递归中添加级别和增量:

WITH NavigationCTE (Id, AnchorText, ParentId, Level) as
(
      Select nav.Id, nav.AnchorText, nav.ParentId, 0 as Level
      from dbo.Navigation AS nav

      UNION ALL 

      select nav.Id, nav.AnchorText, nav.ParentId, Level + 1
      from dbo.Navigation AS nav

      join Navigation AS nav2 ON nav.ParentId = nav2.Id
)


SELECT * FROM NavigationCTE

Answer 1

公用表表达式提供 recursive functionality 类型你需要。研究 的创建和使用等级文章的示例查询中的字段。它完全执行您想要实现的递增类型，从 anchor 查询中的 0 开始。

基于提供的样本的工作查询:

WITH NavigationCTE  AS
(   
    SELECT navA.[Id], navA.ParentId, 0 AS depth_lvl
    FROM Navigation as navA

    UNION ALL

    SELECT navB.Id, navB.ParentId, depth_lvl + 1
    FROM Navigation AS navB
    JOIN NavigationCTE AS nav_cte_a
        --ON navB.ParentId = nav_cte_a.Id
        ON nav_cte_a.ParentId = navB.Id
)
SELECT Id, ParentId, coalesce(max(depth_lvl),0)
FROM NavigationCTE
GROUP BY Id, ParentId
ORDER BY Id, ParentId