当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - 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.

Sample table:

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

Sample data:

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 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

)

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

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

Hopefully that is easy enough to understand.

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

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

网友答案:

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;

EDIT:

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;
分享给朋友:
您可能感兴趣的文章:
随机阅读: