sql server 递归汇总

--汇总追加成本
select csa.AccountLevel ,
	csa.AccountShortName , 
	csa.StageAccountGUID ,
	csa.ParentStageAccountGUID ,
	csa.IsEndCost ,
	csa.ProjGUID ,
	csa.HierarchyCode ,
	isnull(t.AdjustAmount,0) as AdjustAmount
	into #data
from cb_StageAccount csa 
	left join (
		select ctcsvd.StageAccountGUID,
				sum(isnull(cad.AdjustAmount,0)) as AdjustAmount 
		from cb_AdjustDtl cad
			inner join cb_Adjust ca on ca.AdjustGUID = cad.AdjustGUID
			inner join cb_TargetCostStageVersionDetail ctcsvd on cad.TargetStage2CostGUID = ctcsvd.TargetCostStageVersionDetailGUID
			--inner join 过滤，只取最新目标版本数据
			inner join #ctcvPX cp on cp.TargetCostStageVersionGUID = ctcsvd.TargetCostStageVersionGUID
		where ca.AdjustType = '追加目标成本'
		group by ctcsvd.StageAccountGUID
	)t on csa.StageAccountGUID = t.StageAccountGUID
where csa.ProjGUID in (@ProjGUID)
--更新父级
declare @MaxLevel int;
declare @CurrentLevel int;
select @MaxLevel =max(AccountLevel) from #data ;
set @CurrentLevel = @MaxLevel
while @CurrentLevel >= 0
begin
	update a
	SET  a.AdjustAmount = ISNULL((SELECT SUM(b.AdjustAmount) FROM #data b WHERE b.ParentStageAccountGUID=a.StageAccountGUID and b.ProjGUID = a.ProjGUID),a.AdjustAmount)
	from #data a
	where a.AccountLevel = @CurrentLevel
	set @CurrentLevel = @CurrentLevel - 1
end
;

--存在层级的时候，需要按照各级一次网上汇总的时候使用，而不是单一的sum