[SQL语句]超级大难题，极具挑战性。(MRP扣料问题，熟僁的请进)

Question

扣料的原則和顺序能不能改变？
比如先全部按PartCode，不够的才按OldPart等

Answer 1

把問題Copy下來，放到SQL Query analyzer里去看，這樣格式就不會亂。

Answer 2

to yang_,
是按PartID為先後順序(即ORDER BY @rsReqPart.ID ASC,@rsReqPart.PartCode ASC)
一筆處理完之後再轉下一筆。
@rsStockList反映的就是這種情況。

Answer 3

这样的顺序，应该只能用游标的，如果你的@rsReqPart 是个表，或者它的原表的信息足够，可以考虑用触发器来做这个扣料的过程，只要在应用程序限定一次只能插入一条记录，这个操作是可以很好实现的，性能也不必担心。

Answer 4

這是一個物料需求計算的過程，我上面這些table變量都是用來存中間計算結果用的。
實際上整個完整的順序是:
從生產排程中抓取符合條件的製令資料--->依製令從BOM中展開材料并計算每張製令所需材料的需求數--->扣除庫存中的數量--->扣除採購、在途、等待驗收的數量--->最後得出正確的需求數。而且考慮同一時間點多人同時Call這個Stored Procedure的問題(代碼/執行時間都很長，所以沒用Transcation)。
至於用Trigger，好像還不行。

Answer 5

其实这支Stored procedure去年就写好了，长长一串，一仟五伯来行，很吓人，而且经常把Database Server拖得不成人形，所以现在想重写，想找一种不用Cursor的方法。领料需求计算的扣料部份我已经找到不用Cursor的扣料方法了，即用UPDATE语句然后再嵌CASE WHEN 及使用中间变量的方法，结果不错，速度有大幅提高，而物料需求这一块，计算方法较之复杂，瞎搞了两天，还没试出个结果来。

Answer 6

有一个问题先说下我的看法：
你在存储过程里是用的表变量吗？这样复杂的计算，我建议不要用表变量。因为表变量大量占用内存，再加上CURSOR ，反而会比临时表速度慢。
我就试过生成一个有100万条记录的表变量，并运行一个有一点复杂的运算，还只是单机操作就死机了。换成临时表反而速度快多了。

Answer 7

若是换成temp table, SQL2K下会不会增加磁盘I/O?
For best performance, if you need a temporary table in your Transact-SQL code, try to use a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them much faster. In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000] Added 8-7-2001 http://www.sql-server-performance.com/transact_sql.asp

Answer 8

我以前也是这样觉得，用表变量不用读写磁盘，会很快。但现在发现这也不一写的。在表不大的情况下确实如此，但在数据量大的情况下可能就不行了。
如果你这个存储过程会多人使用，而且运行步骤也比较多，时间会比较长，所以会长时间占用大量内存。可能反而用临时表会快些。再加上你还要用CURSOR， 资源肯定会比较紧张。
这个你可以试一下，以实际情况来判断是否用临时表。临时表的IO问题可以用些手段进行调节，如果你的临时表正好空间很小，而且是其它IO操作频繁的文件共用磁盘，那可能是试不出效果的。再关于这个算法的问题，有些想法，但是写起来太要时间了，等我整理一下再说。

Answer 9

当年俺写这个业务也是足足花了三个多星期的时间，后来也是没办法，才用了Cursor这种笨方法。

Answer 10

我的思路是这样的：
1、先不管替代料品的问题。按rsReqPart.id从小到大的优先顺序，分配现有库存。
得到一个粗步分配表，如下（把这个分配的算法叫“算法A”）：
table dist_0:
分配序号  id      partcode      分配量
-------------------------------------
1         1       A1P1          100
2         2       A1P3          30
3         3       A2P1          20
4         4       A2P2          10
5         5       A1P1          0分配后的库存余额：
table stock_0:
partcode       库存余额
--------------------------
A1P1           0
A1P2           140
A1P3           60
A2P1           0
A2P2           02、再考虑替代料品的问题。
现在可以得到第一次分配后的需求量,并做一些相关统计：
table req_0:
id      partcode 分配后的需求量 有无替代品 替代品 替代品有无分配给优先级低的ID
------------------------------------------------------------------------------
1        A1P1        20         1        A2P1      1
2        A1P3        0          0        无        0
3        A2P1        430        0        无        0
4        A2P2        290        0        无        0
5        A1P1        19         0        无        0
说明：“有无替代品”只按oldpart 判断,若“分配后的需求量”为零，“有无替代品”必须为零。对dist_0与req_0按"dist_0.partcode=req_0.替代品"进行内连接，且加上过滤条件：
req_1.制品>0  and req_1.有无替代品=1 and req_1.替代品有无分配给优先级低的ID=1得到下面的表：
分配序号    partcode  待分配量
-------------------------------
3           A2P1      20并合并stock_0,去掉库存余额为零的，得到stock_1:
分配序号   partcode       库存余额
----------------------------------
null       A1P2           140
null       A1P3           60
3          A2P1           20按req_1过滤得到需求量(条件同上)：req_1
id    partcode    需求量
-------------------------
1     A2P1        20再把stock_1分配给req_1(这个分配算法称为“算法B”)，得到以下分配结果：dist_1
id    partcode    需求量     分配量     分配序号
------------------------------------------------
1     A2P1        20         20         3将这个结果反馈给dist_0，包括新增分配记录及冲减重新分配的替代品。
按这个例子会使dist_0中的记录变成这样：分配序号  id      partcode      分配量
-------------------------------------
1         1       A1P1          100
2         2       A1P3          30
3         3       A2P1          0
4         4       A2P2          10
5         5       A1P1          0
6         1       A2P1          203、再按prepart1,prepart2,prepart3分别重复第二步，直到"req_0.有无替代品"都为零为止。我觉得可能这样是不需要用cursor的了。现在已经很晕了，醒醒再试着写写看吧：）

Answer 11

如果ID是连续的话，可以试一试下面的办法
select @min_i=min([id]),@max_i=max([id]) from @rsReqPart
while @min_i<=@max_i 
  begin
     select @val1=??? ,@val2=???,... @valn=???? from @rsRepart where [id]=@min_i
     /*扣料代码
*/
     set @min_i=@min_i+1
  end

Answer 12

to 按钮jj,
   有个问题就是，实际要求是要一笔处理完之后才能转到下一笔，譬如说rsReqPart.ID=1的那一笔，分配了A1P1的库存100个之后，那它的需求数还有20个，然后接下又要再去扣旧料A2P1的库存，分配A2P1的20个后，它的需求数变为了0，这样才能转到rsReqPart.ID=2这一笔上做处理。

Answer 13

我知道啊，但是你是将所有ID处理完再将结果全部返回是不是？我的算法是，先按partcode分配，就是先分100个给id=1的单，有20个先分给ID=3的，第二步，发现id=1的还缺料，就从ID=3那里把缺的那部分拿过来。最后的结果与用游标的结果是一样的。如果你是分配完一个就提交一个记录，那就非用游标不可了。

Answer 14

哦，沒細看。:P
我先try看看。

Answer 15

整了两天了，还是没看懂，好晕啊。
对dist_0与req_0按"dist_0.partcode=req_0.替代品"进行内连接，且加上过滤条件：
req_1.制品>0  and req_1.有无替代品=1 and req_1.替代品有无分配给优先级低的ID=1
>>>>
and req_1.替代品有无分配给优先级低的id=1，这一条件是什幺意思?而且我上面的例子搞错了，不好意思。
实际上为，在扣材料时，顺序应该为这样:
先扣旧料(条件为旧料的库存数要大于等于材料的需求数)若不满足条件则不扣旧料，直接去扣材料及代用料。
但这个过程数据可能会这样子的:
PartCode   OldPart   PrePart1 PrePart2 PrePart3
A1P1      A2P1     A1P3   A4P1   A1P5
A2P1      A1P1     A1P3   A4P1   A1P5
A1P4      A2P1     A1P3   A4P1   A1P5
所以按上面的办法，不知道能不能得到正确结果?

Answer 16

2 laverke(laverke) ,
  没错，PartID是一个IDENTITY(1,1)字段，它的值是连续的。可是不知道同样用Loop的方式去处理扣料过程，速度会有多大的提高。我相信结果可能会是一样快。

Answer 17

没时间看仔细，不用CURSOR，就用临时表，有时候还非得要CURSOR

Answer 18

因为ID=1有替代品为A2P1嘛，而在dist_0中：
3         3       A2P1          20
已经将它分配给id=3的记录了，所以“req_1.替代品有无分配给优先级低的id”就是1，其它的没有这种情况，就是零了。按你的新的分配原则，应该只是先分配一下旧料，再将分配后的库存、及剩余的需求量按原来的分配方法再继续分配。没太大区别，只是第二步不再考虑oldpart字段，只考虑prepart1,prepart2,prepart3就行了。