BOM分解问题,急死人了

Question

你需要怎么样的结果，你的问题，可以用游标解决！树型结构，一般用游标=========================================================
我的回复,尽可能为你分忧解难
BLOG:blog.csdn.net/softj      --欢迎光临，有更多信息等着你！
QQ高级群:5063844专研数据库    --大家进来聊一聊！
MSN:[email protected]             --这不常用！
Mail:[email protected]            --有什么问题可以和我来EMAIL！
=========================================================

Answer 1

是樣的,
  A3產品的派工單(領料單)有如下數據:
   A5   2
   A6   1生成A5,A6采購單
A5    采购       2个
A6    采购       1个  TKS

Answer 2

其它的表,如Production,A3的領料單如下
[編號]  [產品編號]      [產品名稱]  [領用數量]
1        A5                            2
2        A6                            1其它依類推

Answer 3

--生成测试数据
create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20))
insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'
insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'
insert into BOM_1 select 3 ,'A2','A3',3,'生产'
insert into BOM_1 select 4 ,'A2','A4',2,'采购'
insert into BOM_1 select 5 ,'A3','A5',2,'采购'
insert into BOM_1 select 6 ,'A3','A6',1,'采购'
insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'
insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'
insert into BOM_1 select 9 ,'B2','B3',3,'生产'
insert into BOM_1 select 10,'B2','B4',2,'采购'
insert into BOM_1 select 11,'B3','B5',2,'采购'
insert into BOM_1 select 12,'B3','B6',2,'采购'
go
--创建用户定义函数，用于取每个父节点下子节点的采购配置信息
create function f_stock(@bom_head varchar(20))
returns @t table(bom varchar(20),number int)
as
begin 
    declare @level int
    declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)
    set @level=1    if exists(select 1 from BOM_1 where bom_head=@bom_head)    
    insert into @a 
    select bom_child,number,products_attribute,@level 
    from BOM_1 
    where bom_head=@bom_head
    
    while exists(select 1 from @a where [level]=@level and products_attribute='生产')
    begin
        set @level=@level+1
        insert into @a(bom,number,products_attribute,[level])
        select a.bom_child,a.number,a.products_attribute,@level 
        from BOM_1 a,@a b
        where a.bom_head=b.bom and b.[level]=@level-1
    end
    
    insert into @t(bom,number) select bom,number from @a where products_attribute='采购'
    return
end
go
--执行调用，取父节点'A'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A')
/*
bom                  number      
-------------------- ----------- 
A1                   1
A4                   2
A5                   2
A6                   1
*/--执行调用，取父节点'A1'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A1')
/*
bom                  number      
-------------------- -----------
*/--执行调用，取父节点'A2'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A2')
/*
bom                  number      
-------------------- ----------- 
A4                   2
A5                   2
A6                   1
*/--执行调用，取父节点'A3'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A3')
/*
bom                  number      
-------------------- ----------- 
A5                   2
A6                   1
*/
go
--删除测试数据
drop function f_stock
drop table BOM_1

Answer 4

我測試一試,謝謝 libin_ftsafe(子陌红尘) (

Answer 5

create table bom_1(Item int identity(1,1),bom_head varchar(2),bom_child varchar(2),number int,products_attribute varchar(4))
insert bom_1
select 'A','A1',         1,'采购' union all
select 'A','A2',         2,'生产' union all
select 'A2','A3',         3,'生产' union all
select 'A2','A4',         2,'采购' union all
select 'A3','A5',         2,'采购' union all
select 'A3','A6',         1,'采购' union all
select 'B','B1',         1,'采购' union all
select 'B','B2',         2,'生产' union all
select 'B2','B3',         3,'生产' union all
select 'B2','B4',         2,'采购' union all
select 'B3','B5',         2,'采购' union all
select 'B3','B6',      2,'采购'--求A的，举个例子
--假如都是0库存，需求生产A的个数为100
declare @t table(bom_head varchar(2),bom_child varchar(2),number int,products_attribute varchar(6),[level] int)
declare @i int
set @i=0
insert @t select '_0','A',100,'生产单',@i
while exists (select 1 from bom_1 a,@t b where a.bom_head=b.bom_child and b.level=@i)-- and b.ed<>@col )
  begin
   set @i=@i+1
   insert @t 
    select b.bom_child,a.bom_child,a.number*b.number,a.products_attribute+'单',@i from bom_1 a,@t b
    where b.level=@i-1 and a.bom_head=b.bom_child 
end
select * from @t--结果
/*
bom_head bom_child number      products_attribute level       
-------- --------- ----------- ------------------ ----------- 
_0       A         100         生产单                0
A        A1        100         采购单                1
A        A2        200         生产单                1
A2       A3        600         生产单                2
A2       A4        400         采购单                2
A3       A5        1200        采购单                3
A3       A6        600         采购单                3
*/

Answer 6

create table BOM_1(Item int,bom_head varchar(6),bom_child varchar(6),number decimal(28,4),products_attribute varchar(10))
insert into BOM_1
select 1, 'A', 'A1', 1, '采购'union all
select 2, 'A', 'A2', 2, '生产'union all
select 3, 'A2', 'A3', 3, '生产'union all
select 4, 'A2', 'A4', 2, '采购'union all
select 5, 'A3', 'A5', 2, '采购'union all
select 6, 'A3', 'A6', 1, '采购'union all
select 7, 'B', 'B1', 1, '采购'union all
select 8, 'B', 'B2', 2, '生产'union all
select 9, 'B2', 'B3', 3, '生产'union all
select 10, 'B2', 'B4', 2, '采购'union all
select 11, 'B3', 'B5', 2, '采购'union all
select 12, 'B3', 'B6', 2, '采购'
CREATE function dbo.GetSubBomAndAttribute (@bom_head varchar(6),@qty int)
returns @re table(bom varchar(6),myLevel int,products_attribute varchar(10),number decimal(28,4))
as
begin
declare @l int
set @l=0
insert @re select @bom_head,@l,'成品',@qty
while @@rowcount>0  --返回受上一语句影响的行数
begin
set @l=@l+1
insert @re select a.bom_child,@l,a.products_attribute,a.number*b.number
from bom_1 a (nolock)join @re b on a.bom_head=b.bom
where b.myLevel=@l-1
end
return
end
--测试：
select bom as 项目编码,products_attribute as 物品属性,number as 需要采购或生产量 from dbo.GetSubBomAndAttribute('A',4)/*
项目编码   物品属性       需要采购或生产量                       
------ ---------- ------------------------------ 
A      成品         4.0000
A1     采购         4.0000
A2     生产         8.0000
A3     生产         24.0000
A4     采购         16.0000
A5     采购         48.0000
A6     采购         24.0000
*/

Answer 7

还有一种情况，就是比较复杂的BOM，可能会碰到重复的原材料，如上例中假设
A1也是由A3和A4组成，即在bom_1表中多插入2条记录，示例如下：
create table BOM_1(Item int,bom_head varchar(6),bom_child varchar(6),number decimal(28,4),products_attribute varchar(10))
insert into BOM_1
select 1, 'A', 'A1', 1, '采购'union all
select 2, 'A', 'A2', 2, '生产'union all
select 3, 'A1', 'A3', 3, '生产'union all
select 4, 'A1', 'A4', 2, '采购'union all
select 3, 'A2', 'A3', 3, '生产'union all
select 4, 'A2', 'A4', 2, '采购'union all
select 5, 'A3', 'A5', 2, '采购'union all
select 6, 'A3', 'A6', 1, '采购'union all
select 7, 'B', 'B1', 1, '采购'union all
select 8, 'B', 'B2', 2, '生产'union all
select 9, 'B2', 'B3', 3, '生产'union all
select 10, 'B2', 'B4', 2, '采购'union all
select 11, 'B3', 'B5', 2, '采购'union all
select 12, 'B3', 'B6', 2, '采购'
CREATE function dbo.GetSubBomAndAttribute (@bom_head varchar(6),@qty int)
returns @re table(bom varchar(6),myLevel int,products_attribute varchar(10),number decimal(28,4))
as
begin
declare @l int
set @l=0
insert @re select @bom_head,@l,'成品',@qty
while @@rowcount>0  --返回受上一语句影响的行数
begin
set @l=@l+1
insert @re select a.bom_child,@l,a.products_attribute,a.number*b.number
from bom_1 a (nolock)join @re b on a.bom_head=b.bom
where b.myLevel=@l-1
end
return
end
--测试
select bom as 项目编码,products_attribute as 物品属性,sum(number) as 需要采购或生产量 from dbo.GetSubBomAndAttribute('A',4)
group by bom,products_attribute
/*
项目编码   物品属性       需要采购或生产量                                 
------ ---------- ---------------------------------------- 
A1     采购         4.0000
A4     采购         24.0000
A5     采购         72.0000
A6     采购         36.0000
A      成品         4.0000
A2     生产         8.0000
A3     生产         36.0000（所影响的行数为 7 行）
*/