BOM成本计算成最下级算到最上级

高度难动态BOM成本计算
成本计算成最下级算到最上级的。

BOM表
        产品    产品数量  原料          原料数  单价
BILLID  MGOODSID  MQTY  DGOODSID ITEMNO  DQTY  DPRICE
584    A001        1    B001      1      1      ??  (半成品)   
584    A001        1    C001      2      2      5    (原料)    2个C001组成
584    A001        1    D001      3      1      5  (原料)
588    B001        1    E001      1      1      3    (原料)
588    B001        1    F001      2      2      ??    (半成品)  2个F001组成
560    F001        1    G001      1      1      2  (原料)
560    F001        1    J001      2      2      2  (原料)    2个J001组成

树型结构如下:
                      A001  *1
            |              |            |
        B001 *1          C001 *2    D001  *1
    |        |
  E001 *1    F001 *2
            |      |       
          G001 *1  J001 *2


求:
1、要求结果按物料清单最底层原料起计算推算出半成品、成品的单价,希望能做成函数或存储过程。

MGOODSID  MQTY,  DGOODSID,  DQTY,  DPRICE,  DAM
A001        1      B001      1      15        15  ---B001单价是算出来的
A001        1      C001      2      5        10
A001        1      D001      1      5        5
B001        1      E001      1      3        3
B001        1      F001      2      6        12  ---F001单价是算出来的
F001        1      G001      1      2        2 
F001        1      J001      2      2        4

2、如果BOM数据量比较大时上面的方法运算就比较慢了，有没有办法只查询某货品如 A001时,
只计算跟A001相关货品。
WHERE M.GOODSID='A001'时的结果如下：
MGOODSID  MQTY,  DGOODSID, DQTY,  DPRICE,  DAM
A001        1      B001      1      15    15  ---B001单价是算出来的
A001        1      C001      2      5      10
A001        1      D001      1      5      5

 

 

－－－－－－－－－－－－－－－－－－－－

--> 生成测试数据表:BOM

If not object_id('[BOM]') is null
    Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,0.0 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,0.0 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1
Go
--Select * from BOM

-->SQL查询如下:

If not object_id('[Fn_BOM]') is null
    Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
    declare @lvl int
    set @lvl=0
    insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
    while @@rowcount>0
        begin
            set @lvl=@lvl+1
            insert @t
            select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
            from BOM a,@t b
            where a.MGoodsID=b.DGoodsID
                and b.lvl=@lvl-1
        end
    declare @i int
    select @i=max(lvl) from @t where dprice =0
    while @i>=0
        begin
            update a set
                dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/dqty,
                damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
            from @t a
            where lvl=@i
                and dprice =0
            set @i=@i-1
        end
    update @t set DAMT=DQTY*DPRICE where DAMT is null
    return
end
go
select * from fn_bom('a001')
/*
MGOODSID             MQTY                                    DGOODSID             ITEMNO                                  DQTY                                    DPRICE                                  DAMT                                    lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001                 100.00                                  B001                 1.00                                    100.00                                  3.00                                    300.00                                  0
A001                 100.00                                  C001                 2.00                                    100.00                                  1.00                                    100.00                                  0
A001                 100.00                                  D001                 3.00                                    100.00                                  1.00                                    100.00                                  0
B001                 100.00                                  E001                 1.00                                    100.00                                  1.00                                    100.00                                  1
B001                 100.00                                  F001                 2.00                                    100.00                                  2.00                                    200.00                                  1
F001                 100.00                                  G001                 1.00                                    100.00                                  1.00                                    100.00                                  2
F001                 100.00                                  J001                 2.00                                    100.00                                  1.00                                    100.00                                  2

(7 行受影响)
*/

--计算成本存储过程
If not object_id('[Sp_BOM]') is null
    Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID,MQTY
GO

--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID             MQTY                                    DAMT
-------------------- --------------------------------------- ---------------------------------------
B001                 100.00                                  300.00

(1 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID             MQTY                                    DAMT
-------------------- --------------------------------------- ---------------------------------------
A001                 100.00                                  500.00

(1 行受影响)
*/

--计算成本存储过程
If not object_id('[Sp_BOM]') is null
    Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO

--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID             MQTY                                    DGOODSID             DQTY                                    DPRICE                                  DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
B001                 100.00                                  E001                 100.00                                  1.00                                    100.00
B001                 100.00                                  F001                 100.00                                  2.00                                    200.00

(2 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID             MQTY                                    DGOODSID             DQTY                                    DPRICE                                  DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A001                 100.00                                  B001                 100.00                                  3.00                                    300.00
A001                 100.00                                  C001                 100.00                                  1.00                                    100.00
A001                 100.00                                  D001                 100.00                                  1.00                                    100.00

(3 行受影响)
*/

http://topic.csdn.net/u/20090828/20/623de9a7-b417-4af5-adff-b1d19f28172d.html?12795