create table ta(
ParID varchar(10), --父ID
ID varchar(10), --PK
Name varchar(20), --
Nums int --父含子的数量
)insert into ta select '000' ,'A' ,rtrim('成品A '),1
insert into ta select 'A' ,'B' ,rtrim('半成品B'),20
insert into ta select 'A' ,'C' ,rtrim('原料C '),20
insert into ta select 'A' ,'D' ,rtrim('半成品D'),20
insert into ta select 'B' ,'C' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品A '),1
insert into ta select 'E' ,'F' ,rtrim('半成品B'),20
insert into ta select 'E' ,'G' ,rtrim('原料C '),20
insert into ta select 'E' ,'H' ,rtrim('半成品D'),20
insert into ta select 'F' ,'G' ,rtrim('原料C '),20select id par,ParID ,ID ,Name ,Nums into #t from ta where ParID = '000'
while exists (select 1 from #t a left join #t b on a.id=b.parid
join ta c on a.id=c.parid where b.parid is null)
insert into #t select a.par,c.parid,c.id,c.name,c.nums*a.nums
from #t a left join #t b on a.id=b.parid join ta c on a.id=c.parid
where b.parid is nullselect a.Par as ParID,a.ID,a.Name, sum(a.Nums) as Nums
from #t a left join #t b on a.id=b.parid
where b.parid is null
group by a.par,a.ID,a.Name
order by a.par,a.ID--删除测试数据
drop table ta
drop table #tParID ID Name Nums
---------- ---------- -------------------- -----------
A C 原料C 420
A D 半成品D 20
E G 原料C 420
E H 半成品D 20(所影响的行数为 4 行)
ParID varchar(10), --父ID
ID varchar(10), --PK
Name varchar(20), --
Nums int --父含子的数量
)insert into ta select '000' ,'A' ,rtrim('成品A '),1
insert into ta select 'A' ,'B' ,rtrim('半成品B'),20
insert into ta select 'A' ,'C' ,rtrim('原料C '),20
insert into ta select 'A' ,'D' ,rtrim('半成品D'),20
insert into ta select 'B' ,'C' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品A '),1
insert into ta select 'E' ,'F' ,rtrim('半成品B'),20
insert into ta select 'E' ,'G' ,rtrim('原料C '),20
insert into ta select 'E' ,'H' ,rtrim('半成品D'),20
insert into ta select 'F' ,'G' ,rtrim('原料C '),20select id par,ParID ,ID ,Name ,Nums into #t from ta where ParID = '000'
while exists (select 1 from #t a left join #t b on a.id=b.parid
join ta c on a.id=c.parid where b.parid is null)
insert into #t select a.par,c.parid,c.id,c.name,c.nums*a.nums
from #t a left join #t b on a.id=b.parid join ta c on a.id=c.parid
where b.parid is nullselect a.Par as ParID,a.ID,a.Name, sum(a.Nums) as Nums
from #t a left join #t b on a.id=b.parid
where b.parid is null
group by a.par,a.ID,a.Name
order by a.par,a.ID--删除测试数据
drop table ta
drop table #tParID ID Name Nums
---------- ---------- -------------------- -----------
A C 原料C 420
A D 半成品D 20
E G 原料C 420
E H 半成品D 20(所影响的行数为 4 行)
但我公司系统数据库就是这样建的呀,
create table ta(ParID nvarchar(10),ID nvarchar(10),Name nvarchar(10),Nums int)
insert into ta select '000' ,'A' ,rtrim('成品A '),1
insert into ta select 'A' ,'B' ,rtrim('半成品B'),20
insert into ta select 'A' ,'C' ,rtrim('原料C '),20
insert into ta select 'A' ,'D' ,rtrim('半成品D'),20
insert into ta select 'B' ,'C' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品A '),1
insert into ta select 'E' ,'F' ,rtrim('半成品B'),20
insert into ta select 'E' ,'G' ,rtrim('原料C '),20
insert into ta select 'E' ,'H' ,rtrim('半成品D'),20
insert into ta select 'F' ,'G' ,rtrim('原料C '),20
go--查询处理
select ParID,ID,Nums=sum(Nums)
into #t from ta a
where exists(
select * from ta where ParID='000' and ID=a.ParID)
group by ParID,IDwhile @@rowcount>0
update a set Nums=a.Nums*b.Nums,ID=b.ID
from #t a,(
select ParID,ID,Nums=sum(Nums)
from ta a
where exists(
select * from #t where ID=a.ParID)
group by ParID,ID
)b where a.ID=b.ParIDselect a.ParID,a.ID,a.Name,Nums=sum(b.Nums)
from ta a,#t b
where a.ParID=b.ParID and a.ID=b.ID
group by a.ParID,a.ID,a.Name
drop table #t
go--删除测试
drop table ta/*--测试结果ParID ID Name Nums
---------- ---------- ---------- -----------
A C 原料C 420
A D 半成品D 20
E G 原料C 420
E H 半成品D 20(所影响的行数为 4 行)
--*/
create table ta(ParID nvarchar(10),ID nvarchar(10),Name nvarchar(10),Nums int)
insert into ta select '000' ,'A' ,rtrim('成品A '),1
insert into ta select 'A' ,'B' ,rtrim('半成品B'),20
insert into ta select 'A' ,'C' ,rtrim('原料C '),20
insert into ta select 'A' ,'D' ,rtrim('半成品D'),20
insert into ta select 'B' ,'I' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品A '),1
insert into ta select 'E' ,'F' ,rtrim('半成品B'),20
insert into ta select 'A' ,'G' ,rtrim('原料C '),20
insert into ta select 'I' ,'H' ,rtrim('半成品D'),20
insert into ta select 'H' ,'G' ,rtrim('原料C '),20
go--查询处理
select ParID,ID,Name,Nums
into #t from ta a
where exists(
select * from ta where ParID='000' and ID=a.ParID)while @@rowcount>0
update a set Nums=a.Nums*b.Nums,ID=b.ID,Name=b.Name
from #t a,(
select ParID,ID,Name,Nums
from ta a
where exists(
select * from #t where ID=a.ParID)
)b where a.ID=b.ParIDselect ParID,ID,Name,Nums=sum(Nums)
from #t
group by ParID,ID,Name
drop table #t
go--删除测试
drop table ta/*--测试结果ParID ID Name Nums
---------- ---------- ---------- -----------
A C 原料C 20
A D 半成品D 20
A G 原料C 160020
E F 半成品B 20(所影响的行数为 4 行)
--*/
create table ta(ParID nvarchar(10),ID nvarchar(10),Name nvarchar(10),Nums int)
insert into ta select '000' ,'A' ,rtrim('成品A '),1
insert into ta select 'A' ,'B' ,rtrim('半成品B'),20
insert into ta select 'A' ,'C' ,rtrim('原料C '),20
insert into ta select 'A' ,'D' ,rtrim('半成品D'),20
insert into ta select 'B' ,'I' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品A '),1
insert into ta select 'E' ,'F' ,rtrim('半成品B'),20
insert into ta select 'A' ,'G' ,rtrim('原料C '),20
insert into ta select 'I' ,'H' ,rtrim('半成品D'),20
insert into ta select 'H' ,'G' ,rtrim('原料C '),20
go
create function f_id(@p_ID varchar(10))
returns @re table(ParID varchar(10),ID varchar(10),Name varchar(20),Nums int,level int)
as
begin
declare @ParID varchar(10)
declare @l int
set @l=0
declare c_c cursor for select distinct ID from ta where ParID=@p_ID
open c_c
fetch next from c_c into @ParID
while (@@fetch_status=0)
begininsert @re select ParID,ID,Name,Nums,@l from ta
where ParID=@ParID
while @@rowcount>0
begin
set @l=@l+1
insert into @re select
a.ParID,a.ID,a.Name,a.Nums,@l
from ta a,@re b
where a.ParID=b.ID and b.level=@l-1
update @re set id=a.IDd,name=a.Names,Nums=a.Numss*Nums
from @re b inner join (
select parid as pparid,id as idd,name as names,nums as
numss from @re a where exists
(select 1 from @re where parid=a.id )
and parid=@ParID
union all
select parid,id,name,nums from @re a where exists
(select 1 from @re where id=a.parid )
) a on id=a.pparid delete from @re where parid<>@ParID and level=@lend
fetch next from c_c into @ParID
end
close c_c
deallocate c_creturn
end
go
select ParID,ID,Name, sum(Nums) as Nums from f_id('000') group by ParID,ID,Name
ParID ID Name Nums
A C 原料C 20
A D 半成品D 20
A G 原料C 20
A I 原料C 400
E F 半成品B 20
(所影响的行数为 5 行)
A I 原料C 400的