高度难动态BOM成本计算

[sankyqiu]

高度难动态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 4

2、如果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

[SQL77]

[Quote=引用 5 楼 sankyqiu 的回复:]
的确有点麻烦，有哪位高手能帮忙处理解决呀？谢谢！
[/Quote]
TONY哥来看看

[htl258_Tony]

SQL2000还是2005?

[misfeng]

先展BOM，然后进行SUM。

[guguda2008]

T-MAC没醒？他的那个BOM帖呢

[fuxiaoyang13]

学习学习！帮顶

[sankyqiu]

谢谢！是MSSQL 2000的.

[zc_0101]

楼主辛苦了，也很执着，大家帮忙看看吧，这个帖子他至少发了4次了。

[licry01]

[Quote=引用 25 楼 zc_0101 的回复:]
引用 22 楼 sankyqiu 的回复:
谢谢，在我的实际的数据库里面运行比较慢.

不知道除了临时表还有其他思路不。
[/Quote]

尽量把要用的数据调到内存里面， 通俗的就是用很长很的子查询， select。。。。from （select。。。from。。。）这样会减少硬盘io的开销， 不过呢， 代价就是内存用得多，sql语句逻辑复杂/难理解， 且提升的性能有限。

像这样的树型结构， 我一般都会多加一个字段， 在insert和update的时候计算好每个结点的路径， 一般的需求， 层次都不会很多， 路径也不会超过900， 这样就可以创建索引了， 有了索引， 性能提升得太明显了。

如果需求比较变态， 树的层次比较多， 就算是不能建索引， 通过路径字段把所有子结点提取出来的计算量也要比递归检索计算小（大数据量的情况）。

对进、销、存业务逻辑不熟悉， 很难正确理解该问题， 就不献丑了， 期待熟悉这方面业务的老手出来帮下忙喽。

[snwgija]

楼主...同命运的人.最近我也在做这个...我计算的还要复杂..

[duanzhi1984]

我不知道你的BOM里面的数据有多少GB.

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

[snwgija]

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

[zc_0101]

--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= 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
*/
 

[zc_0101]

楼主测试下

[zc_0101]

唉，楼主太不仗义了，竟然一分不给！

[zc_0101]

我他妈的写了俩小时呢

[sankyqiu]

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

[sankyqiu]

不好意思，分数已经在另外一个贴中给zc_0101兄，多谢你的支持和关注！

[sankyqiu]

为什么在我的实际数据库里面运行如下脚本会有如下出来的，肯定是错的。
因为我是查中间层半成品，而这个半成品下只有一个原料的，为什么下面是重复的呢！

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

[dongguangxiang]

有更好的解决办法，哎。。。。。。。。。都回答完了，就不献丑了

[czhlovehong]

通过低阶码计算更快，罗罗嗦嗦一大堆还是回归了