类似bom问题,sql语句求解

Question

fbh,//编号
fflbh,//父料编号,无父料的,为空
fsl// 数量现在给出一个编号,,求其全部的组成,即它的子料以及子料的子料,都显示出来,
??
求高手解答.

Answer 1

                             FG001
                               |
 ---------------------------------------------------------------------
 |                                  |                                |
SFG001                           SFG002                           SFG003
 |                                   |                                   
-------------------               ---------------------                  
|                 |               |            |      |
WIP001         WIP002           WIP003       WIP004 WIP005       WIP006
|                 |               |
RAW001 RAW002  RAW003      RAW004,RAW005  ............create table ps_mstr(ps_par varchar(10)collate Latin1_General_BIN,ps_comp varchar(10),qty numeric(9,2))--sp_help 'ps_mstr'
insert into ps_mstr
select 'FG001',  'SFG001',     1  union all
select 'FG001' , 'SFG002',     1 union all
select 'FG001'  ,'SFG003',     1 union all
select 'SFG001', 'WIP001',     2 union all
select 'SFG001' ,'WIP002',     2 union all
select 'SFG002' ,'WIP003',     3 union all
select 'SFG002' ,'WIP004',     3 union all
select 'SFG002' ,'WIP005',     2 union all
select 'SFG003' ,'WIP006',     3 union all
select 'WIP001' ,'RAW001',     2.66 union all
select 'WIP001' ,'RAW002'  ,   2.33 union all
select 'WIP002' ,'RAW003'  ,   3.21 union all
select 'WIP003' ,'RAW004'  ,   1.89 union all
select 'WIP003' ,'RAW005'  ,   1.86
create function  f_cid(@ps_par varchar(10))
returns @t_level table(ps_par varchar(10)collate Latin1_General_BIN,ps_comp varchar(10),qty numeric(9,2),level int)
as
begin
declare @level int
set @level=1
insert into @t_level select ps_par,ps_comp,qty,@level from ps_mstr where ps_par=@ps_par collate Latin1_General_BIN
while @@rowcount>0
begin
  set @level=@level+1
  insert into @t_level select a.ps_par, a.ps_comp,a.qty*b.qty,@level
  from ps_mstr a,@t_level b
  where a.ps_par=b.ps_comp collate Latin1_General_BIN--(强制性排序)
  and b.level=@level-1 
endreturn
endGo
--查找FG001下介所有用料數量
select * from f_cid('FG001')