对树数据进行分组、聚合和求和的最佳方法是什么？

给定一个自引用表

Item 
-------------
Id (pk)
ParentId (fk)

具有关联值的相关表

ItemValue
-------------
ItemId (fk)
Amount

和一些样本数据

Item                       ItemValues 
Id      ParentId           ItemId      Amount
--------------------       ----------------------
1       null               1           10
2       1                  3           40
3       1                  3           20
4       2                  4           10
5       2                  5           30
6       null
7       6
8       7

我需要一个存储过程Item.Id并返回直接子项及其所有总和ItemValue.Amounts为了他们，他们的孩子，还有他们的孩子，一直到树下。

例如，如果1被传入，树将是2, 3, 4, 5直接孩子是2, 3输出将是

 ItemId    Amount
 ------------------
 2         40     (values from ItemIds 4 & 5)
 3         60     (values from ItemId 3)

应该采用什么样的方法来实现这种行为？

我正在考虑使用 CTE，但想知道是否有更好/更快的方法。

假设您的层次结构不是太深，这样的递归 CTE 就可以工作：

declare @ParentId int;
set @ParentId = 1;

;with 
  Recurse as (
    select 
      a.Id as DirectChildId
    , a.Id
    from Item a 
    where ParentId = @ParentId
    union all
    select
      b.DirectChildId
    , a.Id
    from Item a 
    join Recurse b on b.Id = a.ParentId
    )
select
  a.DirectChildId, sum(b.Amount) as Amount
from Recurse a
left join ItemValues b on a.Id = b.ItemId
group by
  DirectChildId;

非 CTE 方法需要某种形式的迭代，基于游标或其他形式。由于它是一个存储过程，因此有可能，并且如果有大量数据需要递归，那么只要您适当地对数据进行切片，它可能会更好地扩展。

如果聚集索引在Id上，则在ParentId上添加非聚集索引。作为覆盖索引，它将满足无需书签查找的初始查找。聚集索引将有助于递归连接。

如果 ParentId 上已有聚集索引，请在 Id 上添加非聚集索引。它们加起来实际上等同于上述内容。对于 ItemValues，如果实际表比此宽，您可能需要 (ItemId) INCLUDE (Amount) 上的索引。