除了针对预算和修订预算列的该值本身设置的任何值之外,我还需要将每个级别都作为所有子级(在层次结构中)的总和.
我已经包含了我的表结构的简化版本和一些示例数据,以说明当前正在生成的内容以及我想要生成的内容.
样品表：
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);
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
解决方法
您的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;
