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

[sufull88]

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

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

[--小F--]

头都看晕了 佩服钻钻

[sufull88]

很好，一定要认认真真测试一下才行。

[华夏小卒]

[子陌红尘]

--生成测试数据
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

[bancxc]

--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 行受影响)
*/

[sufull88]

想再请教一下
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 呢？

[子陌红尘]

    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)  -----判断下一级是否全更新过

[子陌红尘]

跟在 where 之后的条件是为了避免对同一条记录的重复更新；
子查询的条件为避免下级记录未被完全更新，而导致当前级记录被更新值小于正确值。

[sufull88]

[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吧 是的了，是我手工去算时搞错了，谢谢！！

[ying_wzm]

牛人真多，看了我头晕

[子陌红尘]

修改了一下？

--生成测试数据 
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

[XGJ889]

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

[SQL77]

头晕

[sufull88]

今天真好，这么多高手帮我大忙了

[sufull88]

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

[sankyqiu]

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

[bancxc]

为空就用
ISNULL(M_BOMD.USERDEF9,0)=0
ISNULL(M_BOM.USERDEF9,0)=0
判断是否等于0

[sankyqiu]

[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

[lovezx1028]

學習啦。。

[sufull88]

经过细心测试后发现有点小问题
--生成测试数据
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