层次结构中子级别的总和 [英] Sum of child levels total in a Hierarchy

问题描述

除了预算和修订预算列中针对该值本身设置的任何值之外，我还需要使每个级别都是所有子级（层次结构中）的总和。

I need to have each level be the sum of all children (in the hierarchy) in addition to any values set against that value itself for the Budget and Revised Budget columns.

我提供了表格结构的简化版本和一些示例数据，以说明当前正在生产的产品以及我想要生产的产品。

I've included a simplified version of my table structure and some sample data to illustrate what is currently being produced and what I'd like to produce.

示例表格：

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生成层次结构：

CTE SQL to generate Hierarchy:

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生成（简化）：

So, currently my CTE produces (simplified):

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

Link to SQLFiddle with table and initial CTE: http://sqlfiddle.com/#!3/66f8b/4/0

请注意，任何建议的解决方案都需要在SQL Server 2008R2。

Please note, any proposed solution will need to work in SQL Server 2008R2.

推荐答案

您的 ItemNo 似乎具有项目层次结构嵌入其中。但是，第一个值应该是 10而不是 10.01。如果已解决，则以下查询将起作用：

Your ItemNo appears to have the item hierarchy embedded in it. However, the first value should be '10' rather than '10.01'. If this were fixed, the following query would work:

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需要某种不同的方法。递归的想法是为项目的每个可能值（即，以下中的所有内容）生成一个单独的行，然后将这些值汇总在一起。

To do this as a recursive CTE requires a somewhat different approach. The idea of the recursion is to generate a separate row for each possible value for an item (that is, everything below it), and then to aggregate the values together.

以下内容可以满足您的需求，只不过它会按相反的顺序排列级别（我不知道这是否是真正的问题）：

The following does what you need, except it puts the levels in the reverse order (I don't know if that is a real problem):

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;

这篇关于层次结构中子级别的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！