sqlserver cte递归向上统计

数据字典如下

通过sql可以得到如下结果

select yskm,SUM(je) as je from view_dj where swdjh='321311570376851' group by yskm

 

现在项目有个要求，要统计出他们父级科目的金额。

那怎么向上推出他的父级科目代码的金额，

解决思路：

1. 创建一张临时表来保存结果
2. 用sqlserver cte 来查询出自己及父级所有的科目代码，代码如下

   with cte as  
    (  
    select a.kmcode,a.kmname,a.pidkm from sys_km a   where kmcode in 
    ( select t1.yskm  from view_dj  t1 where t1.swdjh='321311570376851' )
    union all   
    select k.kmcode,k.kmname,k.pidkm  from sys_km k inner join cte c on c.pidkm = k.kmcode  
    )
    select * from cte

   查询结果如下：

      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)  from 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 from #tmphjcx order by len(kmcode) desc

查询结果如下

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

declare @kmcode varchar(200)
declare @hjje decimal(18,2)
declare s cursor  --declare 创建游标
static
for select kmcode,hjje from #tmphjcx order by len(kmcode) desc
open s  --打开游标
fetch next from s into @kmcode,@hjje --提取上次提取行的下一行
while(@@fetch_status = 0)
begin
  if( @hjje is null)
     begin
        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 from  #tmphjcx b where pidkm=@kmcode  ) b
	on b.kmcode=a.kmcode where a.kmcode=@kmcode
      end
   fetch next from s into @kmcode,@hjje
end
close s  --关闭游标
deallocate s  --删除游标，释放资源

 

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

 

结果如下 最后：好的想法+技术可以解决一切难题。如要转载请保留原文地址