sql 二叉树子节点向父节点汇总数据

最新推荐文章于 2022-07-14 09:58:51 发布

qq_41063615

最新推荐文章于 2022-07-14 09:58:51 发布

阅读量397

点赞数

分类专栏： sql 文章标签：二叉树 sql

本文链接：https://blog.csdn.net/qq_41063615/article/details/117399627

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

sql 二叉树子节点向父节点汇总数据

DROP table test_2021; 
create table test_2021
(
	id VARCHAR(20) not null,
	ParentId VARCHAR(20) not null,
	code VARCHAR(20) null,
	name VARCHAR(20) null,
	num DECIMAL(12,2) null
)
INSERT into test_2021(id,ParentId,code,name,num) 
VALUES('01','00','001','单位',0),
('02','01','002','子单位',0),
('03','02','003','分部1',9.87),
('04','02','004','分部2',8.54),
('05','02','005','分部3',6.34),
('06','01','006','子单位2',0),
('07','06','007','分部4',10)
//递归生成个长代码，用于汇总金额的计算
;with test as
(
  select Cast(Code as VARCHAR(max)) as test,Name,Code,id,ParentId,num 
	from   test_2021 where  ParentId='00'
  union all
  select j.test +'.'+ t.Code as test,t.Name,t.Code,t.id,t.ParentId,t.num from test j join test_2021 t on j.ID=t.ParentId
)
--金额汇总
select *, case when  num =0 then (SELECT SUM (isnull(num,0))  FROM test WHERE test LIKE b.test + '.%' ) else num end as sumNum 
from test b