各位谁有能输入父项料号，自动展开BOM的SQL语句呀？

仅作参考.
declare @tRelation table(father varchar(20) collate database_default,code varchar(20) collate database_default)

insert into @tRelation select father,code from itt1 where father='T00001' --在临时表里插入产成品


while exists (select code from @tRelation  
              where code in (select father from itt1)--在itt1表里是父字段
              and code not in (select father from @tRelation)) --在临时表里不是父字段
begin
insert into  @tRelation select father,code from itt1 where father in
            (select code from @tRelation
            where code in (select father from itt1)
              and code not in (select father from @tRelation))
continue
end
select * from @tRelation
select * from @tRelation where code not in  (select father from @tRelation )

应该不错,谢谢!

附件1： 最终的查询结果。

附件2： 定义的表的结构。


存储过程1：

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

ALTER   PROC SHOW_BOM(@father varchar(20),@TQty numeric(19,6))
as
begin
        declare @level int
        declare @code varchar(20)
        declare @childnum int
        declare @quantity numeric(19,6)

        set @level=@@nestlevel --嵌套层次
        if @@nestlevel=-1 --顶层
        begin
                set @TQty=1
                insert z_showbom(code,father,childnum,quantity,level1,level2) select @father,@code,0,1,0,'0'
        end
                       
        if @@nestlevel<10 --最多10层BOM结构
        begin
                 --建立临时表为当前父项搜索子项
                declare c1 cursor local for select code,father,childnum,quantity from itt1 where father=@father
                open c1
                fetch c1 into @code,@father,@childnum,@quantity
                while @@fetch_status=0
                begin
                        insert z_showbom(code,father,childnum,quantity,level1,level2,TQty) select @code,@father,@childnum,@quantity,@level-3,replicate('_',(@level-3)*2)+ltrim(str(@level-3)),@quantity*@TQty       
                        exec SHOW_BOM @code,@quantity
                        fetch c1 into @code,@father,@childnum,@quantity
                end
        end

end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


存储过程1：
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER     PROC Ex_BOM(@bom varchar(20))
as
begin
        delete z_showbom
        insert z_showbom(code,father,childnum,quantity,level1,level2) select @bom,'',0,1,0,'0'
        exec show_bom        @bom,1
        if((select count(name)    FROM   sysobjects  where name ='BOMLIST')>0) Drop table BOMLIST
        select IDENTITY(int,1,1) AS ID_Num, t1.* into BOMLIST from z_showbom t1
        select t1.ID_Num, t1.level2,t1.code,t2.itemname,t2.frgnname,t1.quantity,t2.invntryuom,t1.TQty,t2.PhanTom from BOMLIST t1 left outer join oitm t2 on t1.code=t2.itemcode  order by t1.ID_Num
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

其实这些问题早已经解决了,也有很的多的方法,但是都是差不多的思路,
建立表,写存储过程,然后显示,就OK了.

呵呵。差不多！