你的位置:首页 > 数据库

[数据库]sqlserver cte递归向上统计


数据字典如下

通过sql可以得到如下结果

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


 


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

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

解决思路:

  1. 创建一张临时表来保存结果
  2. 用sqlserver cte 来查询出自己及父级所有的科目代码,代码如下
    1 with cte as 2  ( 3 select a.kmcode,a.kmname,a.pidkm from sys_km a  where kmcode in 4 ( select t1.yskm from view_dj t1 where t1.swdjh='321311570376851' )5 union all  6 select k.kmcode,k.kmname,k.pidkm from sys_km k inner join cte c on c.pidkm = k.kmcode 7  )8 select * from cte

    View Code

    查询结果如下:

      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 t1left join sys_km t2 on t2.kmcode=t1.yskm where t1.swdjh=@bmgroup by t2.kmcode,t2.kmname)bon 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 创建游标staticfor select kmcode,hjje from #tmphjcx order by len(kmcode) descopen 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,@hjjeendclose s --关闭游标deallocate s --删除游标,释放资源

 

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

 

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