数据字典如下
通过sql可以得到如下结果
select yskm,SUM(je) as je from view_dj where swdjh='321311570376851' group by yskm
现在项目有个要求,要统计出他们父级科目的金额。
那怎么向上推出他的父级科目代码的金额,
解决思路:
- 创建一张临时表来保存结果
- 用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.查询出我们想要的结果
结果如下 最后:好的想法+技术可以解决一切难题。如要转载请保留原文地址
原标题:sqlserver cte递归向上统计
关键词:sql