各位忙,SQL Server2000bom
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