sql-server - 层次结构中子级别的总和

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

我希望产生结果:

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 中运行。

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;