高难度多版本BOM成本计算

[sankyqiu]

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

[SQLCenter]

计算母件的单价？

[SQLCenter]

算了 这么久没反应 闪人

[sankyqiu]

SQLCenter兄,有时间帮一下我呀,谢谢!

补充一下产品组成:
母件A 版本号A 由（B 、C 、D 版本号D） ， 母件D 版本号D 由E、F*1组成.
母件A 版本号A2 由（B 、C 、D 版本号D2） ，母件D 版本号D2 由E、F*2组成.

[SQLCenter]

先帮你顶上去

[sankyqiu]

coleling:
谢谢您的回复,太感谢低您了,我再测试一下.

[coleling]

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]

引用 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……



谢谢!经测试如果去掉红色部份,计算出来的结果不正确.
补充一下:这个去掉后在原始数据中是正确的,但在我另外一个大的数据库里面计算出来就是不一样!

[sankyqiu]

请问能不做再做个无参数的函数去引用GetBom(@GoodsId varchar(20))这个函数??
谢谢!

[coleling]

[Quote=引用 14 楼 sankyqiu 的回复:]
补充一下:这个去掉后在原始数据中是正确的,但在我另外一个大的数据库里面计算出来就是不一样!
[/Quote]
[Quote=引用 15 楼 sankyqiu 的回复:]
请问能不做再做个无参数的函数去引用GetBom(@GoodsId varchar(20))这个函数??
[/Quote]
to #14:
这个我就无能为力了，毕竟你的大数据库是什么样子我也不晓得。
to #15:
无参数的函数引用GetBom(@GoodsId varchar(20))的时候，@GoodsId这个参数从哪里来？

可以说一下你为什么一定要去掉这个参数吗？

[sankyqiu]

谢谢你的回复,因为我这个里有个软件只能做接口是没有参数的函数,通过没有参数的函数后出报表时可以很灵活的进行货品的过滤查找.

[jiao3630]

[sankyqiu]

有参数的函数create function GetBom(@GoodsId varchar(20))算出来是正确的,
但去掉参数后的函数就是不对不知道为什么create function GetBom)

[coleling]

改成没参数的了,你再试试：

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]

好的,我再试试,谢谢你的大力帮忙!

[sankyqiu]

coleling:
您好!请问可以再帮忙一下把这个有参数的函数改为没有参数getbom()的函数吗?
如: select * from getbom() ---- WHERE MGOODSID='A' (这里的WHERE让我自己控制)

[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.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]

谢谢!请问对以下有疑问
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

[sankyqiu]

经测试事情可以解决了,非常感谢coleling你的帮忙.

[coleling]

这里是有意不一致的，因为逻辑不一样嘛，如果一样的话，我都会放进循环中去了。
结贴，速度给分吧！

[sankyqiu]

[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]

谢谢!经测试如果去掉红色部份,计算出来的结果不正确.