高度难动态BOM成本计算

Question

高度难动态BOM成本计算
M_BOM 主表M 
BILLID  GOODSID    QTY  PRICE  
584        A001     1     0    
588        B001     1     0       
560        F001     1     0      M_BOMD 细表D 
BILLID GOODSID     ITEMNO  QTY  PRICE 
584        B001      1      1      ??  (半成品)     
584       C001       2      2    5    (原料)    2个C001组成
584       D001       3      1    5  (原料) 
588       E001       1      1    3    (原料) 
588       F001       2      2      ??    (半成品)   2个F001组成
560       G001       1      1    2  (原料) 
560      J001        2      2    2  (原料)     2个J001组成条件: 
M_BOM.BILLID=M_BOMD.BILLID  主表和细表的BILLID相同即是同一个清单                       A001  *1
            |              |             | 
        B001 *1           C001 *2     D001  *1
     |        | 
  E001 *1    F001 *2
             |      |         
          G001 *1  J001 *2 
求:
1、要求结果按物料清单最底层原料起计算推算出半成品、成品的单价,希望能做成函数或存储过程。 M.GOODSID  M.QTY, D.GOODSID, D.QTY,D.PRICE,D.AM 
A001        1      B001      1      15     15 
A001        1      C001      2      5      10 
A001        1      D001      1      5      5
B001        1      E001      1      3      3
B001        1      F001      2      6      12  
F001        1      G001      1      2      2   
F001        1      J001      2      2      42、如果BOM数据量比较大时上面的方法运算就比较慢了，有没有办法只查询某货品如 A001时,
只计算跟A001相关货品。
WHERE M.GOODSID='A001'时的结果如下：
M.GOODSID  M.QTY, D.GOODSID, D.QTY,D.PRICE,D.AM 
A001        1      B001      1      15     15 
A001        1      C001      2      5      10 
A001        1      D001      1      5      5

Answer 1

--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-25 11:13:00=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: [M_BOM]
if object_id('[M_BOM]') is not null drop table [M_BOM]
create table [M_BOM] (BILLID int,GOODSID varchar(4),QTY int,PRICE int)
insert into [M_BOM]
select 584,'A001',1,0 union all
select 588,'B001',1,0 union all
select 560,'F001',1,0
go
--> 测试数据: [M_BOMD]
if object_id('[M_BOMD]') is not null drop table [M_BOMD]
create table [M_BOMD] (BILLID int,GOODSID varchar(4),ITEMNO int,QTY int,PRICE int)
insert into [M_BOMD]
select 584,'B001',1,1,null union all
select 584,'C001',2,2,5 union all
select 584,'D001',3,1,5 union all
select 588,'E001',1,1,3 union all
select 588,'F001',2,2,null union all
select 560,'G001',1,1,2 union all
select 560,'J001',2,2,2----------------查询--------------函数一
--说明：输入GOODSID返回父子关系表
IF OBJECT_ID('F_GET') IS NOT NULL DROP FUNCTION F_GET
GO
CREATE FUNCTION F_GET(@GOODSID VARCHAR(8000))
RETURNS @TABLE TABLE(
ID INT IDENTITY,
P_ID INT DEFAULT(0),
GOODSID VARCHAR(8000),
FLAG BIT
)
AS
BEGIN
DECLARE @TEMP_GOODSID VARCHAR(8000),@TEMP_BILLID INT
SELECT @TEMP_BILLID=BILLID FROM [M_BOM] WHERE GOODSID=@GOODSID
IF (SELECT COUNT(*) FROM @TABLE WHERE GOODSID=@GOODSID)=0
BEGIN
IF (SELECT COUNT(*) FROM [M_BOMD] WHERE GOODSID=@GOODSID)=0
INSERT INTO @TABLE SELECT 0,@GOODSID,1
ELSE
INSERT INTO @TABLE SELECT BILLID,@GOODSID,1 FROM [M_BOMD] WHERE GOODSID=@GOODSID
END
DECLARE @TEMP TABLE(ID INT IDENTITY,GOODSID VARCHAR(8000))
INSERT INTO @TEMP SELECT GOODSID FROM [M_BOMD] WHERE BILLID=@TEMP_BILLID
DECLARE @ID INT,@STR VARCHAR(8000)
SET @ID=1
WHILE @ID<=(SELECT MAX(ID) FROM @TEMP)
BEGIN
IF (SELECT COUNT(*) FROM [M_BOM] WHERE GOODSID=(SELECT GOODSID FROM @TEMP WHERE ID=@ID))>0
BEGIN
SELECT @STR=GOODSID FROM @TEMP WHERE ID=@ID
INSERT INTO @TABLE SELECT P_ID,GOODSID,FLAG  FROM DBO.F_GET(@STR)
END
ELSE
INSERT INTO @TABLE SELECT @TEMP_BILLID,GOODSID,0 FROM @TEMP WHERE ID=@ID
SET @ID=@ID+1
END
RETURN
END
--函数二
--说明：返回最终结果
IF OBJECT_ID('F_ZC') IS NOT NULL DROP FUNCTION F_ZC
GO
CREATE FUNCTION F_ZC(@GOODSID VARCHAR(20))
RETURNS @TABLE TABLE(
ID INT IDENTITY PRIMARY KEY,
A_BILLID INT,
A_GOOSID VARCHAR(8000),
A_QTY INT,
B_GOODSID varchar(8000),
B_QTY int,
B_PRICE int,
TOTAL int
)
AS
BEGIN
INSERT INTO @TABLE SELECT A.P_ID,@GOODSID,(SELECT QTY FROM [M_BOM] WHERE GOODSID=@GOODSID),b.GOODSID,b.QTY,B.PRICE,B.QTY*B.PRICE FROM DBO.F_GET(@GOODSID) A 
INNER JOIN [M_BOMD] B ON A.GOODSID=B.GOODSID AND A.FLAG=0 ORDER BY A.ID DESC
DECLARE @TEMP TABLE(ID INT IDENTITY,P_ID INT DEFAULT(0),GOODSID VARCHAR(8000),FLAG BIT)
INSERT INTO @TEMP SELECT P_ID,GOODSID,FLAG FROM DBO.F_GET(@GOODSID) WHERE FLAG=1
DECLARE @I INT
SELECT @I=MAX(ID) FROM @TEMP
WHILE @I>=1
BEGIN
INSERT INTO @TABLE SELECT MAX(C.P_ID),@GOODSID,MAX(A.QTY),MAX(C.GOODSID),MAX(B.QTY),SUM(D.B_QTY*D.B_PRICE),MAX(B.QTY)*(SUM(D.B_QTY*D.B_PRICE)) FROM 
[M_BOM] A,[M_BOMD] B,@TEMP C,@TABLE D WHERE A.GOODSID=C.GOODSID AND A.BILLID=D.A_BILLID AND C.ID=@I AND B.GOODSID=C.GOODSID GROUP BY D.A_BILLID
SET @I=@I-1
END
DELETE FROM @TABLE WHERE A_BILLID<>(SELECT BILLID FROM [M_BOM] WHERE GOODSID=@GOODSID)
RETURN
END
GO--------------正式查询-------------
SELECT * FROM DBO.F_ZC('A001') ORDER BY ID DESC
SELECT * FROM DBO.F_ZC('B001') ORDER BY ID DESC
 
----------------结果--------------
/*
ID A_BILLID A_GOOSID A_QTY B_GOODSID B_QTY B_PRICE TOTAL
7 584 A001 1 B001 1 15 15
2 584 A001 1 C001 2 5 10
1 584 A001 1 D001 1 5 5
*/
 
/*
ID A_BILLID A_GOOSID A_QTY B_GOODSID B_QTY B_PRICE TOTAL
4 588 B001 1 F001 2 6 12
3 588 B001 1 E001 1 3 3
*/
 

Answer 2

zc_0101兄你好，我用的MSSQL2000，
在增加第二个函数时有如下提示：
服务器: 消息 8101，级别 16，状态 1，过程 F_ZC，行 14
仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 '@TABLE' 中为标识列指定显式值。
警告: 已创建表 '@TABLE'，但其最大行大小（16049）超过了每行的最大字节数（8060）。如果结果行长度超过 8060 字节，则此表中行的 INSERT 或 UPDATE 将失败。

Answer 3

zc_0101兄你好，我用的MSSQL2000，
在增加第二个函数时有如下提示：
服务器: 消息 8101，级别 16，状态 1，过程 F_ZC，行 14
仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 '@TABLE' 中为标识列指定显式值。
警告: 已创建表 '@TABLE'，但其最大行大小（16049）超过了每行的最大字节数（8060）。如果结果行长度超过 8060 字节，则此表中行的 INSERT 或 UPDATE 将失败。

Answer 4

IDENTITY_INSERT 为 ON
你在那句前面加上
SET IDENTITY_INSERT @TALBE ON 

Answer 5

--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101    At:2009-08-25 11:13:00=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: [M_BOM]
if object_id('[M_BOM]') is not null drop table [M_BOM]
create table [M_BOM] (BILLID int,GOODSID varchar(4),QTY int,PRICE int)
insert into [M_BOM]
select 584,'A001',1,0 union all
select 588,'B001',1,0 union all
select 560,'F001',1,0
go
--> 测试数据: [M_BOMD]
if object_id('[M_BOMD]') is not null drop table [M_BOMD]
create table [M_BOMD] (BILLID int,GOODSID varchar(4),ITEMNO int,QTY int,PRICE int)
insert into [M_BOMD]
select 584,'B001',1,1,null union all
select 584,'C001',2,2,5 union all
select 584,'D001',3,1,5 union all
select 588,'E001',1,1,3 union all
select 588,'F001',2,2,null union all
select 560,'G001',1,1,2 union all
select 560,'J001',2,2,2----------------查询--------------函数一
--说明：输入GOODSID返回父子关系表
IF OBJECT_ID('F_GET') IS NOT NULL DROP FUNCTION F_GET
GO
CREATE FUNCTION F_GET(@GOODSID VARCHAR(8000))
    RETURNS @TABLE TABLE(
        ID INT IDENTITY,
        P_ID INT DEFAULT(0),
        GOODSID VARCHAR(8000),
        FLAG BIT
    )
AS
BEGIN
    DECLARE @TEMP_GOODSID VARCHAR(8000),@TEMP_BILLID INT
    SELECT @TEMP_BILLID=BILLID FROM [M_BOM] WHERE GOODSID=@GOODSID
    IF (SELECT COUNT(*) FROM @TABLE WHERE GOODSID=@GOODSID)=0
        BEGIN
            IF (SELECT COUNT(*) FROM [M_BOMD] WHERE GOODSID=@GOODSID)=0
                INSERT INTO @TABLE SELECT 0,@GOODSID,1
            ELSE
                INSERT INTO @TABLE SELECT BILLID,@GOODSID,1 FROM [M_BOMD] WHERE GOODSID=@GOODSID
        END
    DECLARE @TEMP TABLE(ID INT IDENTITY,GOODSID VARCHAR(8000))
    INSERT INTO @TEMP SELECT GOODSID FROM [M_BOMD] WHERE BILLID=@TEMP_BILLID
    DECLARE @ID INT,@STR VARCHAR(8000)
    SET @ID=1
    WHILE @ID<=(SELECT MAX(ID) FROM @TEMP)
        BEGIN
            IF (SELECT COUNT(*) FROM [M_BOM] WHERE GOODSID=(SELECT GOODSID FROM @TEMP WHERE ID=@ID))>0
                BEGIN
                    SELECT @STR=GOODSID FROM @TEMP WHERE ID=@ID
                    INSERT INTO @TABLE SELECT P_ID,GOODSID,FLAG  FROM DBO.F_GET(@STR)
                END
            ELSE
                INSERT INTO @TABLE SELECT @TEMP_BILLID,GOODSID,0 FROM @TEMP WHERE ID=@ID
            SET @ID=@ID+1
        END
    RETURN
END
--函数二
--说明：返回最终结果
IF OBJECT_ID('F_ZC') IS NOT NULL DROP FUNCTION F_ZC
GO
CREATE FUNCTION F_ZC(@GOODSID VARCHAR(20))
    RETURNS @TABLE TABLE(
        ID INT IDENTITY PRIMARY KEY,
        A_BILLID INT,
        A_GOOSID VARCHAR(4000),
        A_QTY INT,
        B_GOODSID varchar(4000),
        B_QTY int,
        B_PRICE int,
        TOTAL int
    )
AS
BEGIN
    INSERT INTO @TABLE(A_BILLID,A_GOOSID,A_QTY,B_GOODSID,B_QTY,B_PRICE,TOTAL) SELECT A.P_ID,@GOODSID,(SELECT QTY FROM [M_BOM] WHERE GOODSID=@GOODSID),b.GOODSID,b.QTY,B.PRICE,B.QTY*B.PRICE FROM DBO.F_GET(@GOODSID) A 
        INNER JOIN [M_BOMD] B ON A.GOODSID=B.GOODSID AND A.FLAG=0 ORDER BY A.ID DESC
    DECLARE @TEMP TABLE(ID INT IDENTITY,P_ID INT DEFAULT(0),GOODSID VARCHAR(8000),FLAG BIT)
    INSERT INTO @TEMP SELECT P_ID,GOODSID,FLAG FROM DBO.F_GET(@GOODSID) WHERE FLAG=1
    DECLARE @I INT
    SELECT @I=MAX(ID) FROM @TEMP
    WHILE @I>=1
        BEGIN
            INSERT INTO @TABLE SELECT MAX(C.P_ID),@GOODSID,MAX(A.QTY),MAX(C.GOODSID),MAX(B.QTY),SUM(D.B_QTY*D.B_PRICE),MAX(B.QTY)*(SUM(D.B_QTY*D.B_PRICE)) FROM 
            [M_BOM] A,[M_BOMD] B,@TEMP C,@TABLE D WHERE A.GOODSID=C.GOODSID AND A.BILLID=D.A_BILLID AND C.ID=@I AND B.GOODSID=C.GOODSID GROUP BY D.A_BILLID
            SET @I=@I-1        
        END
    DELETE FROM @TABLE WHERE A_BILLID<>(SELECT BILLID FROM [M_BOM] WHERE GOODSID=@GOODSID)
    RETURN
END
GO--------------正式查询-------------
SELECT * FROM DBO.F_ZC('A001') ORDER BY ID DESC
SELECT * FROM DBO.F_ZC('B001') ORDER BY ID DESC
 
----------------结果--------------
/*
ID    A_BILLID    A_GOOSID    A_QTY    B_GOODSID    B_QTY    B_PRICE    TOTAL
7    584    A001    1    B001    1    15    15
2    584    A001    1    C001    2    5    10
1    584    A001    1    D001    1    5    5
*/
 
/*
ID    A_BILLID    A_GOOSID    A_QTY    B_GOODSID    B_QTY    B_PRICE    TOTAL
4    588    B001    1    F001    2    6    12
3    588    B001    1    E001    1    3    3
*/
SQL_SERVER 2000测试通过……

Answer 6

请问zjcxc（邹建）在吗？能否做个更优化的处理吗？

Answer 7

int select * from 

Answer 8

这里有个贴子，请参考帮忙一下
http://topic.csdn.net/t/20041105/11/3524364.html

Answer 9

我有做过一个类似的，不知道适不适合你用，这里的BOM主表是ICBOM，子表是ICBOMChild
物料主表t_ICItemMaterial ，物料主表上有单价字段，我做的是一个触发器，当更新物料单价时，
触发器根据BOM更新相对应的产品单价CREATE TRIGGER UpdatePlanPrice ON [dbo].[t_ICItemMaterial] 
FOR INSERT, UPDATE
AS
--根据成本BOM更新成品单价
  IF UPDATE(FPrice)
  BEGIN
    UPDATE T1
    SET T1.FPrice=t2.FPrice
    FROM t_ICItemMaterial T1
    INNER JOIN
    (select distinct t1.fitemid from icbom t1 
     inner join icbomchild t2 on t1.finterid=t2.finterid 
     inner join Inserted t3 on t2.fitemid=t3.fitemid) T3 
     ON T1.FItemID=T3.fitemid
    INNER JOIN
    (select t1.fitemid,sum(isnull(t2.fqty*t3.fprice/nullif(t1.fqty,0),0)) FPrice from icbom t1 
     inner join icbomchild t2 on t1.finterid=t2.finterid 
     inner join t_ICItemMaterial t3 on t2.fitemid=t3.fitemid
     group by t1.fitemid) T2 ON T3.fitemid=T2.fitemid
  END

Answer 10

    尽量把要用的数据调到内存里面， 通俗的就是用很长很的子查询， select。from （selectfrom）这样会减少硬盘io的开销， 不过呢， 代价就是内存用得多，sql语句逻辑复杂/难理解， 且提升的性能有限。    像这样的树型结构， 我一般都会多加一个字段， 在insert和update的时候计算好每个结点的路径， 一般的需求， 层次都不会很多， 路径也不会超过900， 这样就可以创建索引了， 有了索引， 性能提升得太明显了。    如果需求比较变态， 树的层次比较多， 就算是不能建索引， 通过路径字段把所有子结点提取出来的计算量也要比递归检索计算小（大数据量的情况）。    对进、销、存业务逻辑不熟悉， 很难正确理解该问题， 就不献丑了， 期待熟悉这方面业务的老手出来帮下忙喽。

Answer 11

我不知道你的BOM里面的数据有多少GB.我们公司的BOM数据也挺大的。主要看你如何进行优化...还有就是你的数据结构是否有问题！

Answer 12

.估计LZ也是ERP之类的吧...呵呵...头疼的事多了.``

Answer 13

在实际数据库中查询有如下提示：
服务器: 消息 512，级别 16，状态 1，过程 F_ZC，行 14
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
语句已终止。

Answer 14

为什么在我的实际数据库里面运行如下脚本会有如下出来的，肯定是错的。
因为我是查中间层半成品，而这个半成品下只有一个原料的，为什么下面是重复的呢！SELECT * FROM DBO.F_ZC('23200') ORDER BY ID DESC
ID    A_BILLID    A_GOOSID    A_QTY    B_GOODSID    B_QTY    B_PRICE    TOTAL
62 27068 23200 1 11591 0 0 0
61 27068 23200 1 11591 0 0 0
60 27068 23200 1 11591 0 0 0
59 27068 23200 1 11591 0 0 0
58 27068 23200 1 11591 0 0 0
57 27068 23200 1 11591 0 0 0
56 27068 23200 1 11591 0 0 0
55 27068 23200 1 11591 0 0 0
54 27068 23200 1 11591 0 0 0
53 27068 23200 1 11591 0 0 0
52 27068 23200 1 11591 0 0 0
51 27068 23200 1 11591 0 0 0
50 27068 23200 1 11591 0 0 0
49 27068 23200 1 11591 0 0 0
48 27068 23200 1 11591 0 0 0
47 27068 23200 1 11591 0 0 0
46 27068 23200 1 11591 0 0 0
45 27068 23200 1 11591 0 0 0
44 27068 23200 1 11591 0 0 0
43 27068 23200 1 11591 0 0 0
42 27068 23200 1 11591 0 0 0
41 27068 23200 1 11591 0 0 0
40 27068 23200 1 11591 0 0 0
39 27068 23200 1 11591 0 0 0
38 27068 23200 1 11591 0 0 0
37 27068 23200 1 11591 0 0 0
36 27068 23200 1 11591 0 0 0
35 27068 23200 1 11591 0 0 0
34 27068 23200 1 11591 0 0 0
33 27068 23200 1 11591 0 0 0
32 27068 23200 1 11591 0 0 0
31 27068 23200 1 11591 0 0 0
30 27068 23200 1 11591 0 0 0
29 27068 23200 1 11591 0 0 0
28 27068 23200 1 11591 0 0 0
27 27068 23200 1 11591 0 0 0
26 27068 23200 1 11591 0 0 0
25 27068 23200 1 11591 0 0 0
24 27068 23200 1 11591 0 0 0
23 27068 23200 1 11591 0 0 0
22 27068 23200 1 11591 0 0 0
21 27068 23200 1 11591 0 0 0
20 27068 23200 1 11591 0 0 0
19 27068 23200 1 11591 0 0 0
18 27068 23200 1 11591 0 0 0
17 27068 23200 1 11591 0 0 0
16 27068 23200 1 11591 0 0 0
15 27068 23200 1 11591 0 0 0
14 27068 23200 1 11591 0 0 0
13 27068 23200 1 11591 0 0 0
12 27068 23200 1 11591 0 0 0
11 27068 23200 1 11591 0 0 0
10 27068 23200 1 11591 0 0 0
9 27068 23200 1 11591 0 0 0
8 27068 23200 1 11591 0 0 0
7 27068 23200 1 11591 0 0 0
6 27068 23200 1 11591 0 0 0
5 27068 23200 1 11591 0 0 0
4 27068 23200 1 11591 0 0 0
3 27068 23200 1 11591 0 0 0
2 27068 23200 1 11591 0 0 0
1 27068 23200 1 11591 0 0 0