BOM展开问题

[appleller]

bom_parent(bomid,parentid) BOMID与母件ID
1 , A
2 , B
3 , C
bom_child(bomid,childid,qty) BOMID与子件ID与使用用量
1 , A1 , 10
1 , B , 2
1 , A2 , 1
2 , C , 1
3 , D , 2
3 , E , 1
3 , A1 , 1

1根据录入的parentid展开相应的领料推算
2直接根据数据查询出所有parentid的领料推算汇总表
结果：
parentbomid , superparentid , childid , qty
1 , A , A1 , 12
1 , A , A2 , 1
1 , A , D , 4
1 , A , E , 2

2 , B , A1 , 1
2 , B , D , 2
2 , B , E , 1

3 , C , A1 , 1
3 , C , E , 2
3 , C , E , 1

[dawugui]

去精华帖里面找找相关内容.

[appleller]

没找到合适的,能否赐教,谢谢!
SQL2000

[billpu]

2000看看这个
http://blog.csdn.net/zjcxc/archive/2007/08/29/1763598.aspx
http://blog.csdn.net/zjcxc/archive/2005/05/31/384939.aspx
http://blog.csdn.net/zjcxc/archive/2004/09/18/108876.aspx

[appleller]

查到几种写法:

create view pbbom
as
select v_bom_parent_rpt.invcode as finishpart,v_bom_opcomponent_rpt.invcode as material,
v_bom_opcomponent_rpt.baseqtyn/v_bom_opcomponent_rpt.baseqtyd as num from v_bom_parent_rpt inner join v_bom_opcomponent_rpt
on v_bom_parent_rpt.bomid=v_bom_opcomponent_rpt.bomid

--sql2000-1

select finishpart,material,num
into #a
from pbbom
--where finishpart = 'A'

select * into #result
from #a

while exists (select 1 from pbbom t,#a a where a.material = t.finishpart)
begin
select finishpart=a.finishpart,material=t.material,num=t.num*a.num
into #b
from pbbom t,#a a
where a.material = t.finishpart

truncate table #a

insert into #a
select finishpart,material,num
from #b

insert into #result
select * from #a

drop table #b
end

select #result.* from #result left join inventory on #result.material=inventory.cinvcode
where inventory.bself=0 order by finishpart,material



drop table #a
drop table #result

--sql2000-2

CREATE function [dbo].[CalculateProductNeedAll] (@finishpart varchar(25),
@vol decimal(28,2) )
returns @re table(finishpart varchar(25),
vol decimal(28,2),
material varchar(25),
num decimal(28,5)
)
as
begin
declare @tb table(finishpart varchar(25),
material varchar(25),
num decimal(28,5),
CodeLevel int,
LastNum decimal(28,5) )
declare @l int
set @l=0
insert @tb
select @finishpart,
material,
num,
@l,
@vol*num
from pbbom
where finishpart=@finishpart
while @@rowcount>0
begin
set @l=@l+1
insert @tb
select t.material,
pr.material,
pr.num,
@l,
t.LastNum*pr.num as LastNum
from @tb t
join pbbom pr
on t.material=pr.finishpart
and t.CodeLevel=@l-1
end
insert @re
select @finishpart,
@vol,
v.material,
sum(w.LastNum)
from (
select material from @tb group by material
)v
left join @tb w
on v.material=w.material
group by v.material
return
end

SELECT * FROM [dbo].[CalculateProductNeedAll]('1A',100)


sql2005

;with cte as
(select *,t.finishpart as part,cast(t.num as decimal(28, 6)) as sumnum from pbbom t
where not exists(select 1 from pbbom where material=t.finishpart)
union all
select a.*,b.part,cast((a.num*b.sumnum) as decimal(28, 6)) from pbbom a , cte b where a.finishpart=b.material
)
select part,material,sumnum from cte order by part,material