22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tbTest --drop table tbtest
(id nvarchar(5),
parentID nvarchar(5),
score decimal(18,2)
)
insert into tbTest
select '01','',10
union all
select '02','01',30
union all
select '03','02',30
union all
select '04','01',50
union all
select '05','',10
union all
select '06','05',30
declare @level_tt table(id nvarchar(1000),parentID nvarchar(1000),level int)
declare @level int
set @level=0
insert @level_tt(id,parentID,level)
select id,id,@level from tbTest where isnull(parentID,'')=''
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt(id,parentID,level)
select a.id,cast(b.parentID as varchar)+cast(a.id as varchar),@level
from tbTest a,@level_tt b
where a.parentID=b.id and b.level=@level-1
end
select * from @level_tt
select a.id,a.parentID,a.score,SUM(c.score) as 汇总
from tbTest a,@level_tt b,tbTest c,@level_tt d
where a.ID=b.ID and c.ID=d.ID
and d.parentID like b.parentID+'%'
group by a.ID,a.parentID,a.score
order by a.parentID
/*
id parentID score 汇总
01 10.00 120.00
05 10.00 40.00
02 01 30.00 60.00
04 01 50.00 50.00
03 02 30.00 30.00
06 05 30.00 30.00
*/
这样就对了
双编号逐级汇总
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0
drop table tb
go
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000),num decimal(14,2))
go
insert tb
select '0001',null,'云南省',100
union all select '0002','0001','昆明市',200
union all select '0003','0001','昭通市',300.3
union all select '0009','0001','大理市',400.4
union all select '0008',null,'四川省',500.5
union all select '0004',null,'贵州省',600.6
union all select '0005','0002','五华区',101.1
union all select '0007','0003','水富县',102.1
union all select '0006','0005','西园路192号',202.1
union all select '0010','0006','金色梧桐3-702',202.2
union all select '0011','0010','昆空科技有限公司',303.1
union all select '0015','0007','两碗乡',303.2
union all select '0013','0015','两碗村',303.4
union all select '0012','0013','某跨国集团董事',444.1
union all select '0014','0008','成都市',111.10
declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
declare @level int
set @level=0
insert @level_tt(ybh,ebh,level)
select ybh,ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt(ybh,ebh,level)
select a.ybh,b.ebh+a.ybh,@level
from tb a,@level_tt b
where a.ebh=b.ybh and b.level=@level-1
end
select a.ybh,a.ebh,a.beizhu,a.num,SUM(a.num) as 汇总
from tb a,@level_tt b,tb c,@level_tt d
where a.ybh=b.ybh and c.ybh=d.ybh
and d.ebh like b.ebh+'%'
group by a.ybh,a.ebh,a.beizhu,a.num
order by a.ebh
/*
ybh ebh beizhu num 汇总
0001 NULL 云南省 100.00 1200.00
0004 NULL 贵州省 600.60 600.60
0008 NULL 四川省 500.50 1001.00
0002 0001 昆明市 200.00 1000.00
0003 0001 昭通市 300.30 1501.50
0009 0001 大理市 400.40 400.40
0005 0002 五华区 101.10 404.40
0007 0003 水富县 102.10 408.40
0006 0005 西园路192号 202.10 606.30
0010 0006 金色梧桐3-702 202.20 404.40
0015 0007 两碗乡 303.20 909.60
0014 0008 成都市 111.10 111.10
0011 0010 昆空科技有限公司 303.10 303.10
0012 0013 某跨国集团董事 444.10 444.10
0013 0015 两碗村 303.40 606.80
*/
这样行吗?;with liang as
(
select *,total = score from tbTest as a
where not exists(select * from tbTest where a.id = parentid)
union all
select a.*,cast(b.total + a.score as decimal(18,2))
from tbTest as a
join liang as b
on a.id = b.parentid
)
select a.id,a.parentid,isnull(b.score,a.score) as score
from tbTest as a
left join (select id,parentid,sum(total) as score
from liang group by id,parentid) as b
on a.id=b.id and a.parentid=b.parentid