各位幫忙,SQL Server2000編寫bom
BOM的簡單結構為:A(B,C), C(D,E) E(F,G)
要求輸入A數量,直接生成一個TABLE表,顯示物料清單B,C,D,E,F,G
請教各位大俠,謝謝!
[解决办法]
--這個嗎?
create table T (parent_part varchar(10),child_part varchar(10))
insert into T
select 'A ', 'B ' union all
select 'A ', 'C ' union all
select 'C ', 'D ' union all
select 'C ', 'E ' union all
select 'E ', 'F ' union all
select 'E ', 'G '
GO
/*建立function,查找指定節點的所有子結點*/
Create function fn_bom(@parent varchar(10))
returns @t table(part varchar(10),level int)
AS
begin
declare @level int
set @level=1
insert into @t select @parent,@level
while @@rowcount> 0
begin
set @level=@level+1
insert into @t
select a.child_part,@level
from T a,@t b
where a.parent_part=b.part
and b.level=@level-1
end
return
end
GO
--找出A的子結點
select part from dbo.fn_bom( 'A ')
where level> 1
/*
part
----------
B
C
D
E
F
G
*/
drop table t
drop function fn_bom
