--计算的存储过程
create proc p_calc
asselect id,ParentID,Amount,lj=cast(-1 as decimal(18,2)) into #t
from 表--分级计算
update #t set lj=Amount
from #t a
where not exists(
select 1 from #t where ParentID=a.id)
while @@rowcount>0
update #t set lj=isnull(a.Amount,0)+isnull(b.lj,0)
from #t a join(
select ParentID,lj=sum(lj) from #t
where lj>=0 group by ParentID
)b on a.id=b.ParentID
where a.lj=-1 and not exists(
select 1 from #t where ParentID=a.id and lj=-1)select id,ParentID,lj from #t
go--调用存储过程进行计算
exec p_calc
go
create proc p_calc
asselect id,ParentID,Amount,lj=cast(-1 as decimal(18,2)) into #t
from 表--分级计算
update #t set lj=Amount
from #t a
where not exists(
select 1 from #t where ParentID=a.id)
while @@rowcount>0
update #t set lj=isnull(a.Amount,0)+isnull(b.lj,0)
from #t a join(
select ParentID,lj=sum(lj) from #t
where lj>=0 group by ParentID
)b on a.id=b.ParentID
where a.lj=-1 and not exists(
select 1 from #t where ParentID=a.id and lj=-1)select id,ParentID,lj from #t
go--调用存储过程进行计算
exec p_calc
go
create table 表(id int,ParentID int,Name varchar(10),Amount numeric(18,2))
insert 表 select 1, 0, '大类A', 0
union all select 2, 1, '中类A', 0
union all select 3, 2, '小类A', 10
union all select 4, 2, '小类B', 20
union all select 5, 1, '中类B', 0
union all select 6, 5, '小类A', 11
union all select 7, 5, '小类B', 22
go--计算的存储过程
create proc p_calc
asselect id,ParentID,Amount,lj=cast(-1 as decimal(18,2)) into #t
from 表--分级计算
update #t set lj=Amount
from #t a
where not exists(
select 1 from #t where ParentID=a.id)
while @@rowcount>0
update #t set lj=isnull(a.Amount,0)+isnull(b.lj,0)
from #t a join(
select ParentID,lj=sum(lj) from #t
where lj>=0 group by ParentID
)b on a.id=b.ParentID
where a.lj=-1 and not exists(
select 1 from #t where ParentID=a.id and lj=-1)select id,ParentID,lj from #t
go--调用存储过程进行计算
exec p_calc
go--删除测试
drop table 表
drop proc p_calc/*--测试结果id ParentID lj
----------- ----------- --------------------
1 0 63.00
2 1 30.00
3 2 10.00
4 2 20.00
5 1 33.00
6 5 11.00
7 5 22.00(所影响的行数为 7 行)
--*/
create table 表(id int,ParentID int,Name varchar(10),Amount numeric(18,2))
insert 表 select 1, 0, '大类A', 0
union all select 2, 1, '中类A', 0
union all select 3, 2, '小类A', 10
union all select 4, 2, '小类B', 20
union all select 5, 1, '中类B', 0
union all select 6, 5, '小类A', 11
union all select 7, 5, '小类B', 22
go--计算的存储过程
create proc p_calc
asselect id,ParentID,Amount,name,lj=cast(-1 as decimal(18,2)) into #t
from 表--分级计算
update #t set lj=Amount
from #t a
where not exists(
select 1 from #t where ParentID=a.id)
while @@rowcount>0
update #t set lj=isnull(a.Amount,0)+isnull(b.lj,0)
from #t a join(
select ParentID,lj=sum(lj) from #t
where lj>=0 group by ParentID
)b on a.id=b.ParentID
where a.lj=-1 and not exists(
select 1 from #t where ParentID=a.id and lj=-1)select id,ParentID,name,lj from #t
go--调用存储过程进行计算
exec p_calc
goselect * from 表drop table 表
drop proc p_calc