层级结构中子级别总和

Question

我需要每个层级都是该层级（在层次结构中）所有子项的和，加上针对预算和修订预算列设置的任何值。

我已经包含了我的表格结构的简化版本和一些示例数据，以说明目前正在生成什么以及我想要生成什么。

样例表格：

CREATE TABLE Item (ID INT, ParentItemID INT NULL, ItemNo nvarchar(10), ItemName nvarchar(max), Budget decimal(18, 4), RevisedBudget decimal(18, 4));

样本数据：

INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (1, NULL, N'10.01', N'Master Bob', 0.00, 17.00);
INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (2, 1, N'10.01.01', N'Bob 1', 0.00, 0.00);
INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (3, 2, N'10.01.02', N'Bob 2', 2.00, 2.00);
INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (4, 2, N'10.02.01', N'Bob 1.1', 1.00, 1.00);

使用CTE SQL生成层次结构：

WITH HierarchicalCTE
AS
(
    SELECT ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget, 0 AS LEVEL
    FROM Item
    WHERE Item.ParentItemID IS NULL

    UNION ALL

    SELECT i.ID, i.ParentItemID, i.ItemNo, i.ItemName, i.Budget, i.RevisedBudget, cte.LEVEL + 1
    FROM HierarchicalCTE cte
    INNER JOIN Item i ON i.ParentItemID = cte.ID
)

所以，目前我的CTE生成的结果（简化）如下：

ID: 1, Level: 0, Budget: 0, RevisedBudget: 17
ID: 2, Level: 1, Budget: 0, RevisedBudget: 0
ID: 3, Level: 2, Budget: 2, RevisedBudget: 2
ID: 4, Level: 2, Budget: 1, RevisedBudget: 1

And I want the results to produce:

ID: 1, Level: 0, Budget: 3, RevisedBudget: 20
ID: 2, Level: 1, Budget: 3, RevisedBudget: 3
ID: 3, Level: 2, Budget: 2, RevisedBudget: 2
ID: 4, Level: 2, Budget: 1, RevisedBudget: 1

希望这很容易理解。

带有表和初始CTE的SQLFiddle链接：http://sqlfiddle.com/#!3/66f8b/4/0。

请注意，任何提出的解决方案都需要在SQL Server 2008R2中运行。

- Will L

Answer 1

你的ItemNo看起来已经包含了嵌入其中的商品层次结构，但是第一个值应该是'10'而不是'10.01'。如果修正了这个问题，以下查询将会生效：

select i.ID, i.ParentItemID, i.ItemNo, i.ItemName,
       sum(isum.Budget) as Budget,
       sum(isum.RevisedBudget) as RevisedBudget
from item i left outer join
     item isum
     on isum.ItemNo like i.ItemNo+'%'
group by i.ID, i.ParentItemID, i.ItemNo, i.ItemName;

编辑:

要使用递归CTE执行此操作，需要采用略有不同的方法。 递归的想法是为项的每个可能值（也就是所有它下面的内容）生成一个单独的行，然后将这些值聚合在一起。

以下内容可以实现您所需的功能，但是它会以相反的顺序放置级别（我不知道这是否是一个真正的问题）：

WITH HierarchicalCTE AS
(
    SELECT ID, ParentItemID, ItemNo, ItemName,
           Budget, RevisedBudget, 0 AS LEVEL
    FROM Item i
    UNION ALL
    SELECT i.ID, i.ParentItemID, i.ItemNo, i.ItemName,
           cte.Budget, cte.RevisedBudget,
           cte.LEVEL + 1
    FROM HierarchicalCTE cte join
         Item i
         ON i.ID = cte.ParentItemID
)
select ID, ParentItemID, ItemNo, ItemName,
       sum(Budget) as Budget, sum(RevisedBudget) as RevisedBudget,
       max(level)
from HierarchicalCTE
group by ID, ParentItemID, ItemNo, ItemName;

- Gordon Linoff

---

你的第二个答案使用CTE非常好。我已经接受它作为答案。 - Will L