求存储过程更新物料清单成本单价!

sufull88 2009-10-22 01:58:12
求存储过程更新物料清单成本单价!

A 组成
| | |
B C D
| |
E F
| |
J K


M 组成
| | |
E G P
| |
R S --2个

物料清单主表 M_BOM
BILLID BOMVER GOODSID QTY USERDEF9
1001 1001 A 1 0
1002 1002 B 1 0
1003 1003 E 1 0
1004 1004 M 1 0
1005 1005 G 1 0

物料清单细表 M_BOMD -----说明M_BOM.BILLID=M_BOMD.BILLID 则是同一个物料清单
BILLID GOODSID QTY USERDEF9
1001 B 1 0
1001 C 1 0
1001 D 1 0
1002 E 1 0
1002 F 1 0
1003 J 1 0
1003 K 1 0
1004 E 1 0
1004 G 1 0
1004 P 1 0
1005 R 1 0
1005 S 2 0


货品价格表GOODSUNIT
GOODSID PPRICE
A 0
B 0
C 1
D 1
F 1
J 1
K 1
P 1
R 1
S 1


想要的结果如下:
首先根据货品价格表更新物料清单细表 M_BOMD 中的USERDEF9的成本单价,
然后再更新物料清单主表M_BOM 中的USERDEF9的成本金额

物料清单主表 M_BOM
BILLID BOMVER GOODSID QTY USERDEF9
1001 1001 A 1 5
1002 1002 B 1 3
1003 1003 E 1 2
1004 1004 M 1 5
1005 1005 G 1 3

物料清单细表 M_BOMD
BILLID GOODSID QTY USERDEF9
1001 B 1 3
1001 C 1 1
1001 D 1 1
1002 E 1 2
1002 F 1 1
1003 J 1 1
1003 K 1 1
1004 E 1 2
1004 G 1 3
1004 P 1 1
1005 R 1 1
1005 S 2 1
...全文
231 36 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
36 条回复
切换为时间正序
请发表友善的回复…
发表回复
sufull88 2009-10-23
  • 打赏
  • 举报
 回复
经过细心测试后发现有点小问题
--生成测试数据
create table M_BOM(BILLID int,BOMVER int,GOODSID varchar(10),QTY int,USERDEF9 int)
insert into M_BOM select 1001,1001,'A',1,0
insert into M_BOM select 1002,1002,'B',1,0
insert into M_BOM select 1003,1003,'E',1,0
insert into M_BOM select 1004,1004,'M',1,0
insert into M_BOM select 1005,1005,'G',1,0

create table M_BOMD(BILLID int,GOODSID varchar(10),QTY int,USERDEF9 int)
insert into M_BOMD select 1001,'B',1,0
insert into M_BOMD select 1001,'C',1,0
insert into M_BOMD select 1001,'D',1,0
insert into M_BOMD select 1002,'E',1,0
insert into M_BOMD select 1002,'F',1,0
insert into M_BOMD select 1003,'J',1,0
insert into M_BOMD select 1003,'K',1,0
insert into M_BOMD select 1004,'E',1,0
insert into M_BOMD select 1004,'G',1,0
insert into M_BOMD select 1004,'P',1,0
insert into M_BOMD select 1005,'R',1,0
insert into M_BOMD select 1005,'S',2,0

create table GOODSUNIT(GOODSID varchar(10),PPRICE int)
insert into GOODSUNIT select 'A',null ----改这里
insert into GOODSUNIT select 'B',99 ----改这里
insert into GOODSUNIT select 'C',1
insert into GOODSUNIT select 'D',1
insert into GOODSUNIT select 'F',1
insert into GOODSUNIT select 'J',1
insert into GOODSUNIT select 'K',1
insert into GOODSUNIT select 'P',1
insert into GOODSUNIT select 'R',1
insert into GOODSUNIT select 'S',1
go

--更新M_BOMD表BOM最底层价格信息
update a
set
a.USERDEF9=b.PPRICE
from
M_BOMD a,GOODSUNIT b
where
a.GOODSID=b.GOODSID

--逐级更新M_BOMD表BOM各层价格信息
while @@rowcount <>0
begin
update a
set
USERDEF9=(select sum(b.QTY*b.USERDEF9) from M_BOMD b,M_BOM c where a.GOODSID=c.GOODSID and b.BILLID=c.BILLID)
from
M_BOMD a
where
a.USERDEF9=0
and
not exists(select 1 from M_BOMD m,M_BOM n where a.GOODSID=n.GOODSID
and m.BILLID=n.BILLID and m.USERDEF9=0)
end

--更新M_BOM价格信息
update a
set
USERDEF9=(select sum(QTY*USERDEF9) from M_BOMD where BILLID=a.BILLID)
from
M_BOM a

--查看M_BOM更新结果
select * from M_BOM

执行
select * from m_bom m
left join m_bomd d on m.billid=d.billid where m.goodsid='b'

有如下结果
1001 1001 A 1 101 ----这里肯定不对了
1002 1002 B 1 3
1003 1003 E 1 2
1004 1004 M 1 6
1005 1005 G 1 3

1002 1002 B 1 3 1002 E 1 2
1002 1002 B 1 3 1002 F 1 1

lovezx1028 2009-10-23
  • 打赏
  • 举报
 回复
學習啦。。
sankyqiu 2009-10-23
  • 打赏
  • 举报
 回复
[Quote=引用 32 楼 bancxc 的回复:]
为空就用
ISNULL(M_BOMD.USERDEF9,0)=0
ISNULL(M_BOM.USERDEF9,0)=0
判断是否等于0
[/Quote]

好的，谢谢，请问有没有办法可以帮我做成没有参数的函数实时查询产品的成本吗？
BILLID BOMVER MGOODSID MQTY DGOODSID DQTY DUSERDEF9 AM
1001 1001 A 1 B 1 3 3
1001 1001 A 1 C 1 1 1
1001 1001 A 1 D 1 1 1
bancxc 2009-10-23
  • 打赏
  • 举报
 回复
为空就用
ISNULL(M_BOMD.USERDEF9,0)=0
ISNULL(M_BOM.USERDEF9,0)=0
判断是否等于0
sankyqiu 2009-10-23
  • 打赏
  • 举报
 回复
libin_ftsafe 兄，再教一下，
能否做成实时的函数去查询某物料的成本？（即非执行存储过程）
SELECT * FROM (函数) 显示所有物料清单
如需要过滤条件我自己加入说行了。 WHERE MGOODSID='A'

BILLID BOMVER MGOODSID MQTY DGOODSID DQTY DUSERDEF9 AM
1001 1001 A 1 B 1 3 3
1001 1001 A 1 C 1 1 1
1001 1001 A 1 D 1 1 1
sufull88 2009-10-22
  • 打赏
  • 举报
 回复
bancxc兄，你好，谢谢你的回复。如果主表M_BOM和细表M_BOMD 的USERDEF9 是空时则不能更新了。

INSERT INTO M_BOM SELECT
'1001','1001','A','1',0 UNION ALL SELECT
'1002','1002','B','1',0 UNION ALL SELECT
'1003','1003','E','1',0 UNION ALL SELECT
'1004','1004','M','1',0 UNION ALL SELECT
'1005','1005','G','1',0

INSERT INTO M_BOMD SELECT
'1001','B',1,0 UNION ALL SELECT
'1001','C',1,0 UNION ALL SELECT
'1001','D',1,0 UNION ALL SELECT
'1002','E',1,0 UNION ALL SELECT
'1002','F',1,0 UNION ALL SELECT
'1003','J',1,0 UNION ALL SELECT
'1003','K',1,0 UNION ALL SELECT
'1004','E',1,0 UNION ALL SELECT
'1004','G',1,0 UNION ALL SELECT
'1004','P',1,0 UNION ALL SELECT
'1005','R',1,0 UNION ALL SELECT
'1005','S',2,0
sufull88 2009-10-22
  • 打赏
  • 举报
 回复
今天真好，这么多高手帮我大忙了
SQL77 2009-10-22
  • 打赏
  • 举报
 回复
头晕
XGJ889 2009-10-22
  • 打赏
  • 举报
 回复 


DECLARE @FolderList varchar(800)

SET @FolderList='1'

SET NOCOUNT ON

	CREATE TABLE #Temp(FolderId int)	

	INSERT #Temp 

	SELECT FolderId FROM Doc_Folder

	WHERE CHARINDEX(','+LTRIM(FolderId)+',',','+@FolderList+',')>0	

		

	WHILE @@Rowcount>0

	BEGIN

		INSERT #Temp SELECT FolderId FROM Doc_Folder AS A WHERE 

			EXISTS(SELECT 1 FROM #Temp AS B WHERE B.[FolderId]=A.ParentFolderId)

			AND NOT EXISTS(SELECT 1 FROM #Temp AS B WHERE B.[FolderId]=A.[FolderId])

	END			

SELECT * FROM #TEMP

DROP TABLE #TEMP
子陌红尘 2009-10-22
  • 打赏
  • 举报
 回复
修改了一下？


--生成测试数据 

create table M_BOM(BILLID int,BOMVER int,GOODSID varchar(10),QTY int,USERDEF9 int) 

insert into M_BOM select 1001,1001,'A',1,0 

insert into M_BOM select 1002,1002,'B',1,0 

insert into M_BOM select 1003,1003,'E',1,0 

insert into M_BOM select 1004,1004,'M',1,0 

insert into M_BOM select 1005,1005,'G',1,0 



create table M_BOMD(BILLID int,GOODSID varchar(10),QTY int,USERDEF9 int) 

insert into M_BOMD select 1001,'B',1,0 

insert into M_BOMD select 1001,'C',1,0 

insert into M_BOMD select 1001,'D',1,0 

insert into M_BOMD select 1002,'E',1,0 

insert into M_BOMD select 1002,'F',1,0 

insert into M_BOMD select 1003,'J',1,0 

insert into M_BOMD select 1003,'K',1,0 

insert into M_BOMD select 1004,'E',1,0 

insert into M_BOMD select 1004,'G',1,0 

insert into M_BOMD select 1004,'P',1,0 

insert into M_BOMD select 1005,'R',1,0 

insert into M_BOMD select 1005,'S',2,0 



create table GOODSUNIT(GOODSID varchar(10),PPRICE int) 

insert into GOODSUNIT select 'A',0 

insert into GOODSUNIT select 'B',0 

insert into GOODSUNIT select 'C',1 

insert into GOODSUNIT select 'D',1 

insert into GOODSUNIT select 'F',1 

insert into GOODSUNIT select 'J',1 

insert into GOODSUNIT select 'K',1 

insert into GOODSUNIT select 'P',1 

insert into GOODSUNIT select 'R',1 

insert into GOODSUNIT select 'S',1 

go 



--更新M_BOMD表BOM最底层价格信息 

update a 

set 

    a.USERDEF9=b.PPRICE 

from 

    M_BOMD a,GOODSUNIT b 

where 

    a.GOODSID=b.GOODSID 



--逐级更新M_BOMD表BOM各层价格信息 

while @@rowcount <>0 

begin 

  update a 

  set 

      USERDEF9=(select sum(b.QTY*b.USERDEF9) from M_BOMD b,M_BOM c where a.GOODSID=c.GOODSID and b.BILLID=c.BILLID) 

  from 

      M_BOMD a 

  where 

      a.USERDEF9=0 

      and 

      not exists(select 1 from M_BOMD m,M_BOM n where a.GOODSID=n.GOODSID 

                and m.BILLID=n.BILLID and m.USERDEF9=0) 

end 



--更新M_BOM价格信息 

update a 

set 

    USERDEF9=(select sum(QTY*USERDEF9) from M_BOMD where BILLID=a.BILLID) 

from 

    M_BOM a 

    

--查看M_BOM更新结果 

select * from M_BOM 

/* 

BILLID      BOMVER      GOODSID    QTY        USERDEF9    

----------- ----------- ---------- ----------- ----------- 

1001        1001        A          1           5

1002        1002        B          1           3

1003        1003        E          1           2

1004        1004        M          1           6

1005        1005        G          1           3

*/ 



--查看M_BOMD更新结果 

select * from M_BOMD 

/* 

BILLID      GOODSID    QTY        USERDEF9    

----------- ---------- ----------- ----------- 

1001        B          1           3

1001        C          1           1

1001        D          1           1

1002        E          1           2

1002        F          1           1

1003        J          1           1

1003        K          1           1

1004        E          1           2

1004        G          1           3

1004        P          1           1

1005        R          1           1

1005        S          2           1

*/ 

go 



--删除测试数据 

drop table M_BOM,M_BOMD,GOODSUNIT 

go
ying_wzm 2009-10-22
  • 打赏
  • 举报
 回复
牛人真多，看了我头晕
sufull88 2009-10-22
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 bancxc 的回复:]
SQL code--M的单价应该是6吧
--物料清单主表 M_BOMIFOBJECT_ID('M_BOM')ISNOTNULLDROPTABLE M_BOMGOCREATETABLE M_BOM (BILLIDNVARCHAR(20),BOMVERNVARCHAR(20),GOODSIDNVARCHAR(20),QTYINT,USERDEF9INT)GO--物料清单细表 M_BOMDIFOBJEC¡­
[/Quote]

--M的单价应该是6吧 是的了，是我手工去算时搞错了，谢谢！！
子陌红尘 2009-10-22
  • 打赏
  • 举报
 回复
跟在 where 之后的条件是为了避免对同一条记录的重复更新；
子查询的条件为避免下级记录未被完全更新，而导致当前级记录被更新值小于正确值。
子陌红尘 2009-10-22
  • 打赏
  • 举报
 回复 
    M_BOMD a

  where

      a.USERDEF9=0  ----- 判断记录是否被更新过

      and

      not exists(select 1 from M_BOMD m,M_BOM n where a.GOODSID=n.GOODSID

                and m.BILLID=n.BILLID and m.USERDEF9=0)  -----判断下一级是否全更新过
sufull88 2009-10-22
  • 打赏
  • 举报
 回复
想再请教一下
M_BOMD a
where
a.USERDEF9=0 -----请问这里为什么要a.userdef9=0 呢？
and
not exists(select 1 from M_BOMD m,M_BOM n where a.GOODSID=n.GOODSID
and m.BILLID=n.BILLID and m.USERDEF9=0) -----请问这里为什么要a.userdef9=0 呢？
bancxc 2009-10-22
  • 打赏
  • 举报
 回复 


--M的单价应该是6吧

--物料清单主表 M_BOM    

IF OBJECT_ID('M_BOM') IS NOT NULL 

	DROP TABLE M_BOM

GO	

CREATE TABLE M_BOM (BILLID NVARCHAR(20),BOMVER NVARCHAR(20),GOODSID NVARCHAR(20),QTY INT,USERDEF9 INT)

GO

--物料清单细表 M_BOMD

IF OBJECT_ID('M_BOMD') IS NOT NULL 

	DROP TABLE M_BOMD

GO	

CREATE TABLE M_BOMD(BILLID NVARCHAR(20),GOODSID NVARCHAR(20),QTY INT,USERDEF9 INT)

GO

--

IF OBJECT_ID('GOODSUNIT') IS NOT NULL 

	DROP TABLE GOODSUNIT

GO	

CREATE TABLE GOODSUNIT(GOODSID NVARCHAR(20),PPRICE INT)

-----DATA

INSERT INTO M_BOM SELECT  

'1001','1001','A','1',0 UNION ALL SELECT

'1002','1002','B','1',0 UNION ALL SELECT

'1003','1003','E','1',0 UNION ALL SELECT

'1004','1004','M','1',0 UNION ALL SELECT

'1005','1005','G','1',0 

 

INSERT INTO M_BOMD SELECT

'1001','B',1,0 UNION ALL SELECT

'1001','C',1,0 UNION ALL SELECT

'1001','D',1,0 UNION ALL SELECT

'1002','E',1,0 UNION ALL SELECT

'1002','F',1,0 UNION ALL SELECT

'1003','J',1,0 UNION ALL SELECT

'1003','K',1,0 UNION ALL SELECT

'1004','E',1,0 UNION ALL SELECT

'1004','G',1,0 UNION ALL SELECT

'1004','P',1,0 UNION ALL SELECT

'1005','R',1,0 UNION ALL SELECT

'1005','S',2,0 



INSERT INTO GOODSUNIT SELECT 

'A',0  UNION ALL SELECT

'B',0  UNION ALL SELECT

'C',1  UNION ALL SELECT

'D',1  UNION ALL SELECT

'F',1  UNION ALL SELECT

'J',1  UNION ALL SELECT

'K',1  UNION ALL SELECT

'P',1  UNION ALL SELECT

'R',1  UNION ALL SELECT

'S',1 



---先更新末级存货

UPDATE M_BOMD SET USERDEF9 = PPRICE

FROM M_BOMD D

INNER JOIN GOODSUNIT ON D.GOODSID = GOODSUNIT.GOODSID 

WHERE NOT EXISTS(SELECT 1 FROM M_BOM WHERE D.GOODSID=M_BOM.GOODSID)



WHILE @@rowcount<>0

BEGIN 	

	UPDATE M_BOM SET M_BOM.USERDEF9= A.USERDEF9 

	FROM 

	(

		SELECT M.BILLID,USERDEF9 = Sum(D.USERDEF9*D.QTY)

		FROM M_BOM M INNER JOIN M_BOMD D ON M.BILLID=D.BILLID 

		GROUP BY M.BILLID

	)	A , M_BOM

	WHERE A.BILLID=M_BOM.BILLID AND M_BOM.USERDEF9 = 0 

		AND M_BOM.BILLID NOT IN(

		SELECT BILLID FROM M_BOMD WHERE ISNULL(M_BOMD.USERDEF9,0)=0

	)	

		

	UPDATE M_BOMD SET USERDEF9 =M_BOM.USERDEF9 

	FROM M_BOM INNER JOIN M_BOMD ON M_BOMD.GOODSID=M_BOM.GOODSID WHERE M_BOMD.USERDEF9=0 

END

 

select * from M_BOM

select * from M_BOMD

/*BILLID               BOMVER               GOODSID              QTY         USERDEF9

-------------------- -------------------- -------------------- ----------- -----------

1001                 1001                 A                    1           5

1002                 1002                 B                    1           3

1003                 1003                 E                    1           2

1004                 1004                 M                    1           6

1005                 1005                 G                    1           3



(5 行受影响)



BILLID               GOODSID              QTY         USERDEF9

-------------------- -------------------- ----------- -----------

1001                 B                    1           3

1001                 C                    1           1

1001                 D                    1           1

1002                 E                    1           2

1002                 F                    1           1

1003                 J                    1           1

1003                 K                    1           1

1004                 E                    1           2

1004                 G                    1           3

1004                 P                    1           1

1005                 R                    1           1

1005                 S                    2           1



(12 行受影响)

*/
子陌红尘 2009-10-22
  • 打赏
  • 举报
 回复
--生成测试数据
create table M_BOM(BILLID int,BOMVER int,GOODSID varchar(10),QTY int,USERDEF9 int)
insert into M_BOM select 1001,1001,'A',1,0
insert into M_BOM select 1002,1002,'B',1,0
insert into M_BOM select 1003,1003,'E',1,0
insert into M_BOM select 1004,1004,'M',1,0
insert into M_BOM select 1005,1005,'G',1,0

create table M_BOMD(BILLID int,GOODSID varchar(10),QTY int,USERDEF9 int)
insert into M_BOMD select 1001,'B',1,0
insert into M_BOMD select 1001,'C',1,0
insert into M_BOMD select 1001,'D',1,0
insert into M_BOMD select 1002,'E',1,0
insert into M_BOMD select 1002,'F',1,0
insert into M_BOMD select 1003,'J',1,0
insert into M_BOMD select 1003,'K',1,0
insert into M_BOMD select 1004,'E',1,0
insert into M_BOMD select 1004,'G',1,0
insert into M_BOMD select 1004,'P',1,0
insert into M_BOMD select 1005,'R',1,0
insert into M_BOMD select 1005,'S',2,0

create table GOODSUNIT(GOODSID varchar(10),PPRICE int)
insert into GOODSUNIT select 'A',0
insert into GOODSUNIT select 'B',0
insert into GOODSUNIT select 'C',1
insert into GOODSUNIT select 'D',1
insert into GOODSUNIT select 'F',1
insert into GOODSUNIT select 'J',1
insert into GOODSUNIT select 'K',1
insert into GOODSUNIT select 'P',1
insert into GOODSUNIT select 'R',1
insert into GOODSUNIT select 'S',1
go

--更新M_BOMD表BOM最底层价格信息
update a
set
a.USERDEF9=b.PPRICE
from
M_BOMD a,GOODSUNIT b
where
a.GOODSID=b.GOODSID

--逐级更新M_BOMD表BOM各层价格信息
while @@rowcount<>0
begin
update a
set
USERDEF9=(select sum(b.USERDEF9) from M_BOMD b,M_BOM c where a.GOODSID=c.GOODSID and b.BILLID=c.BILLID)
from
M_BOMD a
where
a.USERDEF9=0
and
not exists(select 1 from M_BOMD m,M_BOM n where a.GOODSID=n.GOODSID
and m.BILLID=n.BILLID and m.USERDEF9=0)
end

--更新M_BOM价格信息
update a
set
USERDEF9=(select sum(QTY*USERDEF9) from M_BOMD where BILLID=a.BILLID)
from
M_BOM a

--查看M_BOM更新结果
select * from M_BOM
/*
BILLID BOMVER GOODSID QTY USERDEF9
----------- ----------- ---------- ----------- -----------
1001 1001 A 1 5
1002 1002 B 1 3
1003 1003 E 1 2
1004 1004 M 1 5
1005 1005 G 1 3
*/

--查看M_BOMD更新结果
select * from M_BOMD
/*
BILLID GOODSID QTY USERDEF9
----------- ---------- ----------- -----------
1001 B 1 3
1001 C 1 1
1001 D 1 1
1002 E 1 2
1002 F 1 1
1003 J 1 1
1003 K 1 1
1004 E 1 2
1004 G 1 2
1004 P 1 1
1005 R 1 1
1005 S 2 1
*/
go

--删除测试数据
drop table M_BOM,M_BOMD,GOODSUNIT
go
华夏小卒 2009-10-22
  • 打赏
  • 举报
 回复
sufull88 2009-10-22
  • 打赏
  • 举报
 回复
很好，一定要认认真真测试一下才行。
--小F-- 2009-10-22
  • 打赏
  • 举报
 回复
头都看晕了 佩服钻钻
加载更多回复（15）
易飞9.0ERP一次展多阶BOM清单(含采购单价).txt
易飞ERP 9.0中, 展BOM(多阶)材料清单,通过存储过程获取.借鉴csdn一位博主的经验改进.
sap成本控制[整理].pdf
7. **成本计算方法**：SAP采用物料清单和材料价格计算材料成本，根据工序作业量和单价计算作业成本，通过成本核算单中的比率计算间接费用。这种方法确保了对生产过程的精确成本控制。 8. **生产过程成本控制**：...
1124-基于51单片机的出租车计价器价格调整（昼夜不同价）proteus 原理图、流程图、物料清单、仿真图、源代码.zip
1124-基于51单片机的出租车计价器价格调整（昼夜不同价）proteus 原理图、流程图、物料清单、仿真图、源代码 功能介绍： 出租车计价器： 1、不同情况具有不同的收费标准，具有白天和夜晚不同的计价能力 2、能进行...
基于51单片机的出租车计价器价格调整（昼夜不同价）proteus 原理图、流程图、物料清单、仿真图、源代码
基于51单片机的出租车计价器价格调整（昼夜不同价）proteus 原理图、流程图、物料清单、仿真图、源代码 基于51单片机的出租车计价器（昼夜） 51单片机 出租车计价器： 1、不同情况具有不同的收费标准，具有白天和...
项目成本估算概论.pptx
成本估算的过程包括了多种方法，如类比估算法（基于过去类似项目的成本数据进行预测）、参数模型估计法（利用数学模型估算）、工料清单法（详细列出所有资源和其成本）、软件工具法（使用专门的项目管理软件）以及...
疑难问题

22,301

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章