2023年6月27日发(作者：)

sqlservercte递归向上统计数据字典如下通过sql可以得到如下结果select yskm,SUM(je) as je from view_dj where swdjh='3251' group by yskm

现在项⽬有个要求，要统计出他们⽗级科⽬的⾦额。那怎么向上推出他的⽗级科⽬代码的⾦额，解决思路：1. 创建⼀张临时表来保存结果2. ⽤sqlserver cte 来查询出⾃⼰及⽗级所有的科⽬代码，代码如下1 with cte as

2 (

3 select ,, from sys_km a where kmcode in

4 ( select from view_dj t1 where ='3251' )5 union all

6 select ,, from sys_km k inner join cte c on =

7 )8 select * from cteView Code查询结果如下： 3.在通过cte和⾦额表关联把数据插⼊到临时表中代码如下

insert into #tmphjcx select * from ( select distinct ,,,,i, from cte a

left join(

select ,(+'_'+) as kmname,hjje=sum(je), guoshui=sum(case when ='1' then je end),dishui=sum(case when ='2' then je end) from view_dj t1left join sys_km t2 on = where =@bmgroup by ,)bon =)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 = ,i=i,=

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 = where =@kmcode end fetch next from s into @kmcode,@hjjeendclose s --关闭游标deallocate s --删除游标，释放资源

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

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