sqlserver向上递归统计

select a.kmcode,a.kmname,a.pidkm xx sys_km a where kmcode in ( select t1.yskm from view_dj t1 where t1.swdjh=@bm) union all select k.kmcode,k.kmname,k.pidkm xx sys_km k inner join cte c on c.pidkm = k.kmcode )

     3.在通过cte和金额表关联把数据插入到临时表中代码如下

insert into #tmphjcx select * from ( select  distinct  a.kmcode,a.kmname,a.pidkm,
b.hjje,b.guoshui,b.dishui from cte a
   left join(
 select t2.kmcode,(t2.kmcode+‘_‘+t2.kmname) as kmname,hjje=sum(je), 
guoshui=sum(case when t1.ic=‘1‘ then je end),dishui=sum(case when t1.ic=‘2‘ then je end)  
xx view_dj  t1

left join sys_km  t2 on t2.kmcode=t1.yskm where t1.swdjh=@bm
group by t2.kmcode,t2.kmname)b
on b.kmcode=a.kmcode)c

4.这时查询临时表结果如下，可以看出科目代码顺序现在是对的，但是他们的金额都为空的，怎么根据下一级的科目金额获取上一级的科目金额呢

5,这时我们应该按科目代码的长度倒序排列，逐个更新金额，应该我们只有计算出上一级的金额，然后再上一级金额的基础上计算出下一级金额。（注意：逻辑思想很重要）

select kmcode,hjje xx #tmphjcx order by len(kmcode) desc

结果如下

6。这时我们再更新上面临时表中的记录（创建游标遍历金额表，和临时表关联来更新金额表的值）

update a  set a.hjje= b.hjje,a.guoshui=b.guoshui,a.dishui=b.dishui	    
	from #tmphjcx a
	left join(select sum(hjje) as hjje,sum(dishui) as dishui,
sum(guoshui) as guoshui,@kmcode as kmcode xx  #tmphjcx b where pidkm=@kmcode  ) b
	on b.kmcode=a.kmcode where a.kmcode=@kmcode

7.查询出我们想要的结果

select kmcode,(kmcode+‘_‘+kmname) as kmname,pidkm,isnull(hjje,0) as hjje,
isnull(guoshui,0) as guoshui,isnull(dishui,0) as dishui xx #tmphjcx

结果如下

最后：好的想法+技术可以解决一切难题。如要转载请保留