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