一种BOM所用的Function...

---问题---
create table BOM(CODE varchar(4),C_CODE varchar(4),NUM int)
insert into BOM select 'A','B',2
insert into BOM select 'A','C',1
insert into BOM select 'C','D',2
insert into BOM select 'C','E',4
insert into BOM select 'E','F',3
insert into BOM select 'E','G',2
go

--动态参数化CODE的值
create function f_BOM(@CODE varchar(4),@NUM int)
returns @t table(CODE varchar(4),NUM int)
as
begin
    insert into @t(CODE,NUM) select C_CODE,NUM*@NUM FROM BOM WHERE CODE=@CODE

-- C,1
   
    while @@rowcount<>0
    begin
        insert into @t(CODE,NUM)
        select
            a.C_CODE,a.NUM*b.NUM
        from
            BOM a,@t b
        where
            a.CODE=b.CODE
            and
            not exists(select 1 from @t where CODE=a.C_CODE)
    end
--    Code=C
    return
end
go

select * from dbo.f_BOM('C',1)

 

 

--A                   --1
  |--B                      --2                     
  |--C                      --1             
     |--D              --2                     
     |--E             --4
        |--F              --3
        |--G          --2