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

前端之家收集整理的这篇文章主要介绍了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中工作.

解决方法

您的ItemNo似乎已嵌入项目层次结构.但是,第一个值应该是’10’而不是’10 .01′.如果这已得到修复,以下查询将起作用:
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;
原文链接:https://www.f2er.com/mssql/83677.html

猜你在找的MsSQL相关文章