新年好,继前贴(求一语句,也借过年散分),计算原料问题，各位多多捧场

[ReViSion]

前贴为：
http://community.csdn.net/Expert/topic/3777/3777733.xml?temp=.290951

数据表a
ParID --父ID
ID --
Name --
Nums --父含子的数量

Id为顶级时Ｐarid为‘０００’，Nums=1
name字段命名没有规律

数据如下：
000 A 成品Ａ　　 1
A B 半成品Ｂ　 20
A C 原料Ｃ　　 20
A D 半成品Ｄ　 20
B C 原料C 20

000 Ｅ.....................
.............................

要得到的结果：
Ａ　　Ｃ　　原料Ｃ　　 420 --(20*20+20)
A D 半成品Ｄ　 20
................................

E............................

[ReViSion]

欢迎大家多多捧场，多多交流。
多说多得，不够再开贴。

[lishengyu]

up

[year2005]

你的数据搞反了。应该是原材料的父ID为‘000’才对。因为我们关注的是“某个成品是由哪些半成品、原材料组成的”，而不是关心“某个原材料能生产哪些成品、半成品”。

[lishengyu]

create table ta(
ParID varchar(10), --父ID
ID varchar(10), --PK
Name varchar(20), --
Nums int --父含子的数量
)

insert into ta select '000' ,'A' ,rtrim('成品Ａ　'),1
insert into ta select 'A' ,'B' ,rtrim('半成品Ｂ'),20
insert into ta select 'A' ,'C' ,rtrim('原料Ｃ　'),20
insert into ta select 'A' ,'D' ,rtrim('半成品Ｄ'),20
insert into ta select 'B' ,'C' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品Ａ　'),1
insert into ta select 'E' ,'F' ,rtrim('半成品Ｂ'),20
insert into ta select 'E' ,'G' ,rtrim('原料Ｃ　'),20
insert into ta select 'E' ,'H' ,rtrim('半成品Ｄ'),20
insert into ta select 'F' ,'G' ,rtrim('原料C '),20

select 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 null

select 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 #t

ParID ID Name Nums
---------- ---------- -------------------- -----------
A C 原料Ｃ　 420
A D 半成品Ｄ 20
E G 原料Ｃ　 420
E H 半成品Ｄ 20

（所影响的行数为 4 行）

[didoleo]

--不对了，不应该出现
A I 原料C 400

的

[ReViSion]

To:year2005(2005年的新ID)
但我公司系统数据库就是这样建的呀，

[zjcxc]

--示例数据
create table ta(ParID nvarchar(10),ID nvarchar(10),Name nvarchar(10),Nums int)
insert into ta select '000' ,'A' ,rtrim('成品Ａ　'),1
insert into ta select 'A' ,'B' ,rtrim('半成品Ｂ'),20
insert into ta select 'A' ,'C' ,rtrim('原料Ｃ　'),20
insert into ta select 'A' ,'D' ,rtrim('半成品Ｄ'),20
insert into ta select 'B' ,'C' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品Ａ　'),1
insert into ta select 'E' ,'F' ,rtrim('半成品Ｂ'),20
insert into ta select 'E' ,'G' ,rtrim('原料Ｃ　'),20
insert into ta select 'E' ,'H' ,rtrim('半成品Ｄ'),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,ID

while @@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.ParID

select 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 原料Ｃ　 420
A D 半成品Ｄ 20
E G 原料Ｃ　 420
E H 半成品Ｄ 20

（所影响的行数为 4 行）
--*/

[zjcxc]

--示例数据
create table ta(ParID nvarchar(10),ID nvarchar(10),Name nvarchar(10),Nums int)
insert into ta select '000' ,'A' ,rtrim('成品Ａ　'),1
insert into ta select 'A' ,'B' ,rtrim('半成品Ｂ'),20
insert into ta select 'A' ,'C' ,rtrim('原料Ｃ　'),20
insert into ta select 'A' ,'D' ,rtrim('半成品Ｄ'),20
insert into ta select 'B' ,'I' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品Ａ　'),1
insert into ta select 'E' ,'F' ,rtrim('半成品Ｂ'),20
insert into ta select 'A' ,'G' ,rtrim('原料Ｃ　'),20
insert into ta select 'I' ,'H' ,rtrim('半成品Ｄ'),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.ParID

select 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 原料Ｃ　 20
A D 半成品Ｄ 20
A G 原料C 160020
E F 半成品Ｂ 20

（所影响的行数为 4 行）
--*/

[zjcxc]

第一个理解错了,第二个才对

[didoleo]

--示例数据
create table ta(ParID nvarchar(10),ID nvarchar(10),Name nvarchar(10),Nums int)
insert into ta select '000' ,'A' ,rtrim('成品Ａ　'),1
insert into ta select 'A' ,'B' ,rtrim('半成品Ｂ'),20
insert into ta select 'A' ,'C' ,rtrim('原料Ｃ　'),20
insert into ta select 'A' ,'D' ,rtrim('半成品Ｄ'),20
insert into ta select 'B' ,'I' ,rtrim('原料C '),20
insert into ta select '000' ,'E' ,rtrim('成品Ａ　'),1
insert into ta select 'E' ,'F' ,rtrim('半成品Ｂ'),20
insert into ta select 'A' ,'G' ,rtrim('原料Ｃ　'),20
insert into ta select 'I' ,'H' ,rtrim('半成品Ｄ'),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)
begin

insert @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=@l

end
fetch next from c_c into @ParID
end
close c_c
deallocate c_c

return
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 原料Ｃ　 20
A D 半成品Ｄ 20
A G 原料Ｃ　 20
A I 原料C 400
E F 半成品Ｂ 20


（所影响的行数为 5 行）

[ReViSion]

谢谢各位了

[ReViSion]

我回去测试一下