求函数MSSQL2000多版本BOM表计算产品成本

Question

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
  5 , X , 1 , XXX细表: 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
  5 , 1 , A , 1 , 1 ---这个关联主表M_BOM中BILLID=1
  5 , 2 , A , 2 , 4 ---这个关联主表M_BOM中BILLID=4货品单价表 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 , 2A , 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 , 3X , 1 , XXX , 1 , A , 1 , A ,  4,  4
X , 1 , XXX , 2 , A , 2 , A2 , 5 , 1
以下是由coleling兄回复 请查询如下网址http://topic.csdn.net/u/20101023/22/0e742161-3437-48ec-bffc-bb29864f8413.html
不知道在我的数据库存面运行部分是正确的,但有部分又是不正确的.

Answer 1

wxf163兄,本人刚学MSSQL还请多多帮忙!

Answer 2

现在BOM主、细表添加了部份数据麻烦再帮忙测试一下
 4 , 3 , D , 1 , 3 ---这个关联主表M_BOM中BILLID=3
  5 , 1 , A , 1 , 1 ---这个关联主表M_BOM中BILLID=1
  5 , 2 , A , 2 , 4 ---这个关联主表M_BOM中BILLID=4

Answer 3

    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
    enda.BomIdName, a.Qty, a.BomIdName
不知道是不是这里循环有点问题，货品的编码不一定等于版本号的，请注意。

Answer 4

修改后的版本if object_id('GetBom','TF') is not null
    drop function GetBom
gocreate 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.Id,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
goselect * from dbo.getbom() where mgoodsid = 'x'/*
MGoodsId   MQty        BomVer     ItemNo      GoodsId    Qty         BomIdName  Price       AM
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- -----------
X          1           XXX        1           A          1           A          4           4
X          1           XXX        2           A          2           A2         5           10(2 行受影响)
*/
如OK，记得结贴哦

Answer 5

--感觉没什么问题啊？
select * from dbo.getbom()/*
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
D          1           D          1           E          1           NULL       1           1
D          1           D          2           F          1           NULL       1           1
D          1           D2         1           E          1           NULL       1           1
D          1           D2         2           F          2           NULL       1           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
X          1           XXX        1           A          1           A          4           4
X          1           XXX        2           A          2           A2         5           10(12 行受影响)
*/
把错误的结果发上来我看看

Answer 6

版本号 产品编号 产品名称 货品编码 品名 物料来源 子版本 数量 参考单价 参考金额 最近单价 最近金额
NC563282
NC563282 NC563282 NC黄色修色漆 NCD452 NC白底漆 自制件 NCD452 75.77 10.12 7.67 10.12 7.67
NC563282 NC563282 NC黄色修色漆 NCM-1019R NC高亮光透明面漆 自制件 NCM-1019R 20.66 14.2 2.93 14.17 2.93
NC563282 NC563282 NC黄色修色漆 HV-715 铁黄色浆(PU/NC底面通用) 自制件 HV-715 3.55 13.65 0.48 13.61 0.48
NC563282 NC563282 NC黄色修色漆 HV-711 190氧化铁红色浆 自制件 HV-711 0.01 14.35 0 14.28 0
NC563282 NC563282 NC黄色修色漆 HV-720 黑浆(NC通用) 自制件 HV-720 0.01 22.33 0 22.4 0
NC563282 NC563282 NC黄色修色漆 HV-723 柠檬黄色浆(色深) 自制件 HV-723 0.01 20.69 0 20.62 0
合计 100.01 11.09 11.08


版本号 产品编号 产品名称 货品编码 品名 物料来源 子版本 数量 参考单价 参考金额 最近单价 最近金额
NCD452
NCD452 NCD452 NC白底漆 NCM-008 棉液 自制件 NCM-008 36.5 13.69 5 13.39 4.89
NCD452 NCD452 NC白底漆 R-145 3701醇酸树脂 外购件 10 9 0.9 9 0.9
NCD452 NCD452 NC白底漆 R-116 蓖麻油 外购件 1.5 16.5 0.25 16.5 0.25
NCD452 NCD452 NC白底漆 HV-065 8040防沉蜡浆 自制件 HV-065 2 16.92 0.34 16.78 0.34
NCD452 NCD452 NC白底漆 HV-344 白色浆(NC通用) 自制件 HV-344 14 14.4 2.02 14.3 2
NCD452 NCD452 NC白底漆 HV-3020 亮光白色浆 自制件 HV-3020 5 21.71 1.09 21.33 1.07
NCD452 NCD452 NC白底漆 F-350 1250滑石粉(绅鹏) 外购件 12 1.45 0.17 1.45 0.17
NCD452 NCD452 NC白底漆 F-348 800#滑石粉(绅鹏) 外购件 12 1.4 0.17 1.4 0.17
NCD452 NCD452 NC白底漆 F-310 800目重钙 外购件 3 0.6 0.02 0.6 0.02
NCD452 NCD452 NC白底漆 F-339 硬脂酸锌1819  外购件 2 10 0.2 10 0.2
NCD452 NCD452 NC白底漆 HV-307 SD-1防沉浆 自制件 HV-307 2 12.09 0.24 11.19 0.22
合计 100 10.39 10.22

版本号 产品编号 产品名称 货品编码 品名 物料来源 子版本 数量 参考单价 参考金额 最近单价 最近金额
NCM-1019R
NCM-1019R NCM-1019R NC高亮光透明面漆 NCM-008 棉液 自制件 NCM-008 71 13.69 9.72 13.39 9.51
NCM-1019R NCM-1019R NC高亮光透明面漆 R-102 马林酸树脂1307 外购件 11.6 22.5 2.61 22.3 2.59
NCM-1019R NCM-1019R NC高亮光透明面漆 D-050 KH-10 外购件 0.2 46 0.09 46 0.09
NCM-1019R NCM-1019R NC高亮光透明面漆 R-145 3701醇酸树脂 外购件 15.9 9 1.43 9 1.43
NCM-1019R NCM-1019R NC高亮光透明面漆 R-116 蓖麻油 外购件 1 16.5 0.17 16.5 0.17
NCM-1019R NCM-1019R NC高亮光透明面漆 D-015 BYK-306 外购件 0.3 85 0.26 82 0.25
合计 100 14.27 14.03

版本号 产品编号 产品名称 货品编码 品名 物料来源 子版本 数量 参考单价 参考金额 最近单价 最近金额
HV-715
HV-715 HV-715 铁黄色浆(PU/NC底面通用) R-128 312-70树脂 外购件 50 12.9 6.45 12.8 6.4
HV-715 HV-715 铁黄色浆(PU/NC底面通用) D-044 BYK163 外购件 2 157 3.14 161 3.22
HV-715 HV-715 铁黄色浆(PU/NC底面通用) S-623 环已酮CYC 外购件 3 17.7 0.53 16.5 0.5
HV-715 HV-715 铁黄色浆(PU/NC底面通用) P-715 PM2038铁黄 外购件 30 7.8 2.34 7.8 2.34
HV-715 HV-715 铁黄色浆(PU/NC底面通用) S-662 二甲苯XYL 外购件 15 7.9 1.19 7.7 1.16
合计 100 13.65 13.61可能这样比较难看得到

Answer 7

上下级BOM成本分别查询时是对不上的.

Answer 8

楼主，这应该是数据类型不正确造成的。我写的函数中都是int,算出来肯定有误差。if object_id('GetBom','TF') is not null
    drop function GetBom
gocreate function GetBom()
returns @tb table(MGoodsId Varchar(10), MQty numeric(22,10), BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty numeric(22,10), BomIdName Varchar(10), Price numeric(22,10), AM numeric(22,10)) 
AS
begin
    declare @i int
    set @i = 1
    declare @t table(id int,BillId int, MGoodsId Varchar(10), MQty numeric(22,10), BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty numeric(22,10), BomId int, BomIdName Varchar(10), Price numeric(22,10), AM numeric(22,10), 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.Id,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
go
你再试试。

Answer 9

谢谢回复,经测试数据还是有些不对.
在我的实际数据库中 MGOODSID,GOODSID ,BOMID 都是整数的,即1至99999999999999没有小数位的,
我在举例子时只是为了方便查看,所以这里举例 MGOODSID,GOODSID ,BOMID 都是字符型.create function GetBom()
returns @tb table(MGoodsId int, MQty numeric(22,10), BomVer Varchar(255), ItemNo int, GoodsId int , Qty numeric(22,10), BomIdName Varchar(255), Price numeric(22,10), AM numeric(22,10)) begin
    declare @i int
    set @i = 1
    declare @t table(id int,BillId int, MGoodsId INT , MQty numeric(22,10), BomVer Varchar(255), ItemNo int, GoodsId INT , Qty numeric(22,10), BomId int, BomIdName Varchar(255), Price numeric(22,10), AM numeric(22,10), LevelId int
    while @i <= 10 AND @@RowCount <> 0
    begin
        set @i = @i + 1        insert @t
        select a.Id, b.BillId, a.BomIdName, 
----(估计这里的a.BomIdName可能有问题了,不可能是字符串呀,只能是整数INT呀)
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

Answer 10

a.BomIdName,  
----(估计这里的a.BomIdName可能有问题了,不可能是字符串呀,只能是整数INT呀)

Answer 11

这是我实际帐套里面的函数
CREATE function GetBomx(@FROMDATE DATETIME, @TODATE DATETIME)
returns @tb table(BILLID INT , MGoodsId int , MQty numeric(22,10),
 BomVer Varchar(255), ItemNo int, GoodsId int,
Qty numeric(22,10), BomIdName Varchar(255), Price numeric(22,10),
AM numeric(22,10) , PPRICE numeric(22,10) , CAMT  numeric(22,10))
AS
begin
    declare @i int
    set @i = 1  ---一阶
    declare @t table(id int,  BillId int, MGoodsId VARCHAR(255) ,
    MQty numeric(22,10),  BomVer Varchar(255), ItemNo int,
    GoodsId  Varchar(255),  Qty numeric(22,10),  BomId Varchar(255),
    BomIdName  Varchar(255),  Price numeric(22,10),  AM numeric(22,10),
    LevelId int ,  PPRICE numeric(22,10) ,  CAMT  numeric(22,10))
---Bomid=子件BOMID?bomidname=子件版本名称?
    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  , D.PPRICE ,  B.QTY*D.PPRICE  AS CAMT
    from M_BOMV1 a 
        join M_BOMV2 b ON a.BillId = b.BillId 
        left join M_BOMV1 c ON b.BomId = c.BillId
        join R_GOODSPRICE (@FROMDATE , @TODATE ) d ON b.GoodsId = d.GoodsId    while @i <= 10 AND @@RowCount <> 0
    begin
        set @i = @i + 1  ---二        insert @t
        select a.ID,   b.BillId,  ----a.GoodsId,-----
        a.BomIdName,
        ----a.BillId,   b.BillId, a.BomIdName,
        ----a.ID,   b.BillId,a.GoodsId,----- 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 ,  D.PPRICE ,    B.QTY*D.PPRICE  AS CAMT
        from @t a
            join M_BOMV2 b ON a.BomId = b.BillId
            left join M_BOMV1 c ON b.BomId = c.BillId
            join R_GOODSPRICE (@FROMDATE , @TODATE ) 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 ,
        a.Pprice = b.Ptotal, a.CAMT = a.Qty*b.Ptotal
        from @t a 
join (select id,BillId, sum(Qty*Price) as total ,
          sum(Qty*PPrice) as Ptotal   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(  BILLID, MGoodsId, MQty,
    BomVer,  ItemNo,    GoodsId,
    Qty,     BomIdName,  Price,
    AM ,    PPRICE ,  CAMT        )
    select BILLID, MGoodsId, MQty,
    BomVer, ItemNo, GoodsId,
    Qty, BomIdName, Price,
    AM , PPRICE , CAMT
    
    from @t 
    where levelid = 1 
    order by billid, itemno    return
end