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

除了针对预算和修订预算列的该值本身设置的任何值之外,我还需要将每个级别都作为所有子级(在层次结构中)的总和.

我已经包含了我的表结构的简化版本和一些示例数据,以说明当前正在生成的内容以及我想要生成的内容.

样品表：

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,RevisedBudget) VALUES (2,1,N'10.01.01',N'Bob 1',0.00);
INSERT INTO Item (ID,RevisedBudget) VALUES (3,2,N'10.01.02',N'Bob 2',2.00,2.00);
INSERT INTO Item (ID,RevisedBudget) VALUES (4,N'10.02.01',N'Bob 1.1',1.00,1.00);

CTE SQL生成层次结构：

WITH HierarchicalCTE
AS
(
    SELECT ID,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,RevisedBudget: 0
ID: 3,Level: 2,Budget: 2,RevisedBudget: 2
ID: 4,Budget: 1,RevisedBudget: 1

我想要结果产生：

ID: 1,Budget: 3,RevisedBudget: 20
ID: 2,RevisedBudget: 3
ID: 3,RevisedBudget: 1

希望这很容易理解.

使用表和初始CTE链接到SQLFiddle：http://sqlfiddle.com/#!3/66f8b/4/0

请注意,任何建议的解决方案都需要在SQL Server 2008R2中工作.

解决方法

select i.ID,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.ItemName;

编辑：

要做到这一点,递归CTE需要一种不同的方法.递归的想法是为项的每个可能值(即,它下面的所有值)生成一个单独的行,然后将这些值聚合在一起.

以下是您需要的,除了它以相反的顺序放置级别(我不知道这是否是一个真正的问题)：

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

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!