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

Question

   求存储过程更新物料清单成本单价!
 
                 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
   

Answer 1

为什么又是BOM  有请乌龟

Answer 2

小F，就不必客气了，你最喜欢的BOM.

Answer 3

设计得不大合理啊
扩充货品价格表好了
根据                A 组成 
        |      |    | 
        B      C    D 
      |    | 
      E    F 
    |  | 
    J  K 
              M 组成 
    |        |    | 
    E        G    P 
            |  | 
            R  S --2个 
在价格表GOODSUNIT写入每种货品的价格
更新的时候直接 
update M_BOMD set USERDEF9 = b.PPRICE*a.QTY
from M_BOMD a,GOODSUNIT b
where a.GOODSID = b.GOODSID

Answer 4

不知道这个是以什么形式存储的                A 组成 
        |      |    | 
        B      C    D 
      |    | 
      E    F 
    |  | 
    J  K 
              M 组成 
    |        |    | 
    E        G    P 
            |  | 
            R  S --2个 

Answer 5

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 
goupdate a
set 
    a.USERDEF9=b.PPRICE
from
    M_BOMD a,GOODSUNIT b
where
    a.GOODSID=b.GOODSIDwhile @@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)
endupdate a
set 
    USERDEF9=(select sum(QTY*USERDEF9) from M_BOMD where BILLID=a.BILLID)
from
    M_BOM a
    select * from M_BOMselect * from M_BOMD
godrop table M_BOM,M_BOMD,GOODSUNIT
go/*
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           3BILLID      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
*/

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

修改了一下？--生成测试数据 
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

Answer 12

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

Answer 13

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 

Answer 14

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

Answer 15

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

Answer 16

好的，谢谢，请问有没有办法可以帮我做成没有参数的函数实时查询产品的成本吗？
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

Answer 17

经过细心测试后发现有点小问题
--生成测试数据 
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 31002 1002 B 1 3 1002 E 1 2
1002 1002 B 1 3 1002 F 1 1