高难度多版本BOM成本计算

sankyqiu 2010-10-23 10:19:05
MSSQL2000多版本BOM表计算产品成本
该BOM表采用主、从表设计,当主、从表的BILLID相同时则表示同一阶BOM,
BOM表可能有多阶的,这里写出的数据比较简单,希望能循环计算出多阶BOM单价,
单价从最下阶开始往上累加计算得出。

主表: M_BOM
单据ID , 母件 , 数量 , 版本号
BILLID , GOODSID , QTY , BOMVER
1 , A , 1 , A
2 , D , 1 , D
3 , D , 1 , D2
4 , A , 1 , A2

细表: M_BOMD
单据ID ,子件序号, 子件 , 数量 ,关联主表BILLID
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

货品单价表 GOODS
货品 , 单价
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 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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
经测试事情可以解决了,非常感谢coleling你的帮忙.
回复
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:
您好!请问可以再帮忙一下把这个有参数的函数改为没有参数getbom()的函数吗?
如: select * from getbom() ---- WHERE MGOODSID='A' (这里的WHERE让我自己控制)
回复
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
有参数的函数create function GetBom(@GoodsId varchar(20))算出来是正确的,
但去掉参数后的函数就是不对不知道为什么create function GetBom)

回复
jiao3630 2010-10-26
回复
sankyqiu 2010-10-26
谢谢你的回复,因为我这个里有个软件只能做接口是没有参数的函数,通过没有参数的函数后出报表时可以很灵活的进行货品的过滤查找.
回复
coleling 2010-10-26
[Quote=引用 14 楼 sankyqiu 的回复:]
补充一下:这个去掉后在原始数据中是正确的,但在我另外一个大的数据库里面计算出来就是不一样!
[/Quote]
[Quote=引用 15 楼 sankyqiu 的回复:]
请问能不做再做个无参数的函数去引用GetBom(@GoodsId varchar(20))这个函数??
[/Quote]
to #14:
这个我就无能为力了,毕竟你的大数据库是什么样子我也不晓得。
to #15:
无参数的函数引用GetBom(@GoodsId varchar(20))的时候,@GoodsId这个参数从哪里来?

可以说一下你为什么一定要去掉这个参数吗?
回复
sankyqiu 2010-10-26
请问能不做再做个无参数的函数去引用GetBom(@GoodsId varchar(20))这个函数??
谢谢!
回复
sankyqiu 2010-10-26
引用 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……



谢谢!经测试如果去掉红色部份,计算出来的结果不正确.
补充一下:这个去掉后在原始数据中是正确的,但在我另外一个大的数据库里面计算出来就是不一样!
回复
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兄,有时间帮一下我呀,谢谢!

补充一下产品组成:
母件A 版本号A 由(B 、C 、D 版本号D) , 母件D 版本号D 由E、F*1组成.
母件A 版本号A2 由(B 、C 、D 版本号D2) ,母件D 版本号D2 由E、F*2组成.
回复
SQLCenter 2010-10-23
算了 这么久没反应 闪人
回复
SQLCenter 2010-10-23
计算母件的单价?
回复
加载更多回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-23 10:19
社区公告
暂无公告