# 高难度多版本BOM成本计算

sankyqiu 2010-10-23 10:19:05
MSSQL2000多版本BOM表计算产品成本

BOM表可能有多阶的，这里写出的数据比较简单，希望能循环计算出多阶BOM单价，

BILLID , GOODSID , QTY , BOMVER
1 , A , 1 , A
2 , D , 1 , D
3 , D , 1 , D2
4 , A , 1 , A2

BILLID , ITEMNO ,GOODSID , QTY , BOMID
1 , 1 , B , 1 , NULL
1 , 2 , C , 1 , NULL
1 , 3 , D , 1 , 2 ---这个关联主表M_BOM中BILLID=2
2 , 1 , E , 1 , NULL
2 , 2 , F , 1 , NULL
3 , 1 , E , 1 , NULL
3 , 2 , F , 2 , NULL
4 , 1 , B , 1 , NULL
4 , 2 , C , 1 , NULL
4 , 3 , D , 1 , 3 ---这个关联主表M_BOM中BILLID=3

GOODSID , PRICE
A , 1
B , 1
C , 1
D , 1
E , 1
F , 1

MGOODIS ,MQTY , BOMVER , ITEMNO , GOODSID , QTY , BOMIDNAME , PRICE , AM
A , 1 , A , 1 , B , 1 , NULL , 1 , 1
A , 1 , A , 2 , C , 1 , NULL , 1 , 1
A , 1 , A , 3 , D , 1 , D , 2 , 2

A , 1 , A2 , 1 , B , 1 , NULL , 1 , 1
A , 1 , A2 , 2 , C , 1 , NULL , 1 , 1
A , 1 , A2 , 3 , D , 1 , D2 , 3 , 3

...全文
457 点赞 收藏 22

22 条回复

sankyqiu 2010-10-26
[Quote=引用 10 楼 coleling 的回复:]

1.create function GetBom(@GoodsId varchar(20))的红字部分去掉.
2. insert @t
select a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Pr……
[/Quote]

coleling 2010-10-26

sankyqiu 2010-10-26

sankyqiu 2010-10-26

insert @t
select a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from M_BOM a
join M_BOMD b ON a.BillId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.GoodsId = @GoodsId

while @i <= 10 AND @@RowCount <> 0
begin
set @i = @i + 1

insert @t ----这里跟上面字段内容怎么不一致的?是否正确?
select b.BillId, a.BomIdName, a.Qty, a.BomIdName, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from @t a
join M_BOMD b ON a.BomId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.BomIdName IS NOT NULL AND a.LevelId = @i-1
end

coleling 2010-10-26

1.create function GetBom(@GoodsId varchar(20))的红字部分去掉.
2. insert @t
select a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from M_BOM a
join M_BOMD b ON a.BillId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.GoodsId = @GoodsId红字部分去掉
3.调用的时候这样：select * from getbom() where MGoodsId = 'a'

sankyqiu 2010-10-26
coleling:

sankyqiu 2010-10-26

coleling 2010-10-26

``````
if object_id('GetBom','TF') is not null
drop function GetBom
go

create function GetBom()
returns @tb table(MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomIdName Varchar(10), Price int, AM int)
AS
begin
declare @i int
set @i = 1
declare @t table(id int,BillId int, MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomId int, BomIdName Varchar(10), Price int, AM int, LevelId int)

insert @t
select a.BillId,a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from M_BOM a
join M_BOMD b ON a.BillId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId

while @i <= 10 AND @@RowCount <> 0
begin
set @i = @i + 1

insert @t
select a.BillId,b.BillId, a.BomIdName, a.Qty, a.BomIdName, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from @t a
join M_BOMD b ON a.BomId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.BomIdName IS NOT NULL AND a.LevelId = @i-1
end

select @i = max(levelid) from @t
while @i >= 2
begin
update a set a.price = b.total, a.AM = a.Qty*b.total from @t a join (select id,BillId, sum(Qty*Price) as total from @t where levelid = @i group by id,BillId) b on a.BomId = b.BillId and a.id = b.id where a.levelid = @i-1

set @i = @i - 1
end

insert @tb(MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM)
select MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM
from @t
where levelid = 1
order by billid, itemno

return
end

select * from getbom() where mgoodsid = 'a'
``````

sankyqiu 2010-10-26

jiao3630 2010-10-26

sankyqiu 2010-10-26

coleling 2010-10-26
[Quote=引用 14 楼 sankyqiu 的回复:]

[/Quote]
[Quote=引用 15 楼 sankyqiu 的回复:]

[/Quote]
to #14:

to #15:

sankyqiu 2010-10-26

sankyqiu 2010-10-26

1.create function GetBom(@GoodsId varchar(20))的红字部分去掉.
2. insert @t
select a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Pr……

coleling 2010-10-25
``````
if object_id('GetBom','TF') is not null
drop function GetBom
go

create function GetBom(@GoodsId varchar(20))
returns @tb table(MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomIdName Varchar(10), Price int, AM int)
AS
begin
declare @i int
set @i = 1
declare @t table(BillId int, MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomId int, BomIdName Varchar(10), Price int, AM int, LevelId int)

insert @t
select a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from M_BOM a
join M_BOMD b ON a.BillId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.GoodsId = @GoodsId

while @i <= 10 AND @@RowCount <> 0
begin
set @i = @i + 1

insert @t
select b.BillId, a.BomIdName, a.Qty, a.BomIdName, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from @t a
join M_BOMD b ON a.BomId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.BomIdName IS NOT NULL AND a.LevelId = @i-1
end

select @i = max(levelid) from @t
while @i >= 2
begin
update a set a.price = b.total, a.AM = a.Qty*b.total from @t a join (select BillId, sum(Qty*Price) as total from @t where levelid = @i group by BillId) b on a.BomId = b.BillId where a.levelid = @i-1

set @i = @i - 1
end

insert @tb(MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM)
select MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM
from @t
where levelid = 1
order by billid, itemno

return
end

select * from getbom('A')

/*
MGoodsId   MQty        BomVer     ItemNo      GoodsId    Qty         BomIdName  Price       AM
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- -----------
A          1           A          1           B          1           NULL       1           1
A          1           A          2           C          1           NULL       1           1
A          1           A          3           D          1           D          2           2
A          1           A2         1           B          1           NULL       1           1
A          1           A2         2           C          1           NULL       1           1
A          1           A2         3           D          1           D2         3           3

(6 行受影响)
*/
``````

sankyqiu 2010-10-25
coleling:

SQLCenter 2010-10-24

sankyqiu 2010-10-24
SQLCenter兄,有时间帮一下我呀,谢谢!

SQLCenter 2010-10-23

SQLCenter 2010-10-23

2.1w+

MS-SQL Server 疑难问题

2010-10-23 10:19