11G版本oracle用sql实现多对多的顺序分摊，不能用存储过程，详见截图例子？
发布网友
发布时间：2022-04-13 07:16
共3个回答
懂视网
时间：2022-04-13 11:38
经常会碰到，由于业务需要，需要将某种汇总的数据按照一定的原则分摊给一堆数据。 其实，如果逻辑清晰的话，这类型的程序还是比较好些的。 本文重点是如果用简单的程序实现这种效果，而且不容易分摊分错。 所有的分摊问题，首先必须要搞清楚以下几点问题： 1
经常会碰到，由于业务需要，需要将某种汇总的数据按照一定的原则分摊给一堆数据。其实，如果逻辑清晰的话，这类型的程序还是比较好些的。
本文重点是如果用简单的程序实现这种效果，而且不容易分摊分错。
所有的分摊问题，首先必须要搞清楚以下几点问题：
1 首要的，要确定 什么东西，多少数量 分摊给什么东西？举个形象的例子，一桶沙子分摊给一些瓶子。
2 分摊的先后原则。上面的例子，一桶沙子分摊给一些瓶子，那瓶子的被分摊顺序是什么样子的？沙子先给哪个瓶子？要先确定清楚。
说得好像有点麻烦，举个例子说明。
最近接到的一个需求：
PO入库的时候，批次可能重复输入，所以入库之后，库存已经汇总在一起了。然后用户对(汇总的)库存进行消耗(就是杂发)。
现在需要有个报表可以知道：按照先进先出的原则，区分用户的一段期间内的消耗数量 对应的是那笔入库单号。
备注：假设下面的数量对应都是主单位。
7.1 入库单R1 料号A 批次P1 接收入库 400
7.3 入库单R2 料号A 批次P1 接收入库 300
这时候，P1批次库存共 700
-------消耗(杂发)明细
7.10 消耗P1 100
7.12 消耗P1 200
8.10 消耗P1 200
8.13 消耗P1 100
8.20 消耗P1 50
9.20 消耗P1 50
如果查询报表的日期选择的是：8.1~8.31
8.1号 之前共消耗100+200=300
8.1~8.31号 之内一共消耗：200+100+50=350
所以核心问题是要将350如何分摊在R1和R2里面。
要实现的分摊效果：
入库总数 之前消耗的分摊 期间内消耗的分摊
R1 400 300 100
R2 300 0 250
所以，结果是，报表是：8.1~8.31
一共消耗350,对应入库单的消耗情况：
R1消耗100
R2消耗250
实现逻辑：
你可以假想，现在有2个沙桶，
红色的沙桶装的沙子是 之前消耗的分摊 的数量
黑色的沙桶装的沙子是 期间内消耗的分摊 的数量
每张入库单就是一个瓶子，所以共有2个瓶子，R1和R2。现在是如何将 红色的沙子 和 黑色的沙子 装到这2个瓶子里面。
装沙规则：
1 用沙子的顺序：先用 红色的沙子，用完之后，再用黑色的沙子。
2 装瓶子的顺序：按照先进先出的原则，必须先装瓶子R1，再装R2.
DECLARE
L_PRE_PERIOD_QTY NUMBER; ---期间前的汇总消耗量 ---之前消耗的分摊 的数量---红色的沙子
L_CURR_PERIOD_QTY NUMBER ; --本期的汇总消耗量--期间内消耗的分摊 的数量---黑色的沙子
----装的结果用记录类型存下来，因为后面要用到。
TYPE shipment_consume_Rec_Type IS RECORD
(
SHIPMENT_LINE_ID NUMBER
, PRIMARY_QUANTITY NUMBER
, consume_pre_qty NUMBER
, consume_curr_qty NUMBER
);
TYPE shipment_consume_Tbl_Type IS TABLE OF shipment_consume_Rec_Type
INDEX BY BINARY_INTEGER ;
L_shipment_consume_Tbl shipment_consume_Tbl_Type;
N NUMBER;
BEGIN
----1 首先要算出红色的沙子和黑色的沙子的总数量,就是有多少数量可分摊。
SELECT nvl(sum(case when MMT.transaction_date < :P_F_START_DATE then
ABS(NVL(MTLN.PRIMARY_QUANTITY,0))
else
0
end ),0) PRE_PERIOD_QTY,
nvl(sum(case when MMT.transaction_date >= :P_F_START_DATE then
ABS(NVL(MTLN.PRIMARY_QUANTITY,0))
else
0
end ),0) CURR_PERIOD_QTY
INTO L_PRE_PERIOD_QTY,L_CURR_PERIOD_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT
,MTL_TRANSACTION_LOT_NUMBERS MTLN
WHERE MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID
AND MMT.TRANSACTION_TYPE_ID = 74
AND MMT.TRANSACTION_ACTION_ID = 6
AND MMT.OWNING_TP_TYPE = 1 ---所有权转出的（寄售供应商的库存）
---
AND MMT.ORGANIZATION_ID = 103
AND MMT.INVENTORY_ITEM_ID = 11783561
AND MTLN.LOT_NUMBER = 'P0000001'
AND MMT.transaction_date<=:P_F_END_DATE;
DBMS_OUTPUT.PUT_LINE('L_PRE_PERIOD_QTY:'||L_PRE_PERIOD_QTY||' -L_CURR_PERIOD_QTY:'||L_CURR_PERIOD_QTY);
N := 1;
-----2 分摊主逻辑。
FOR REC_SHIPMENT_LINE IN (
-----瓶子(入库单)的游标
SELECT MMT.ORGANIZATION_ID
,MMT.INVENTORY_ITEM_ID
,MTLN.LOT_NUMBER
,MTLN.TRANSACTION_DATE
,RT.SHIPMENT_HEADER_ID
,RT.SHIPMENT_LINE_ID
,MTLN.PRIMARY_QUANTITY
FROM MTL_TRANSACTION_LOT_NUMBERS MTLN, MTL_MATERIAL_TRANSACTIONS MMT,RCV_TRANSACTIONS RT
WHERE MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND MMT.TRANSACTION_TYPE_ID = 18
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 1
AND XYG_PO_PKG.CHECK_PO_LINE_CONSIGN(RT.PO_LINE_ID) = 'Y'
AND MMT.ORGANIZATION_ID = 103
AND MMT.INVENTORY_ITEM_ID = 11783561
AND MTLN.LOT_NUMBER = 'P0000001'
ORDER BY MTLN.TRANSACTION_DATE,MMT.TRANSACTION_ID) LOOP
---2.1 优先消耗期初之前的耗料数量，就是先用红色的沙子的数量。
IF L_PRE_PERIOD_QTY >= REC_SHIPMENT_LINE.PRIMARY_QUANTITY THEN ---当红色沙子的数量大于瓶子的容量的时候。
L_shipment_consume_Tbl(N).SHIPMENT_LINE_ID :=REC_SHIPMENT_LINE.SHIPMENT_LINE_ID;
L_shipment_consume_Tbl(N).PRIMARY_QUANTITY :=REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
----消耗红沙的数量就是瓶子的容量。
L_shipment_consume_Tbl(N).consume_pre_qty :=REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
L_shipment_consume_Tbl(N).consume_curr_qty :=0;
----期初数量就是剩下要分配的数量。因为红色沙子已经被消耗掉一部分了。
L_PRE_PERIOD_QTY :=L_PRE_PERIOD_QTY-REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
ELSE ---当红色沙子的数量小于瓶子容量的时候
L_shipment_consume_Tbl(N).SHIPMENT_LINE_ID :=REC_SHIPMENT_LINE.SHIPMENT_LINE_ID;
L_shipment_consume_Tbl(N).PRIMARY_QUANTITY :=REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
----瓶子装 红色沙子的数量就是红色沙子的数量了
L_shipment_consume_Tbl(N).consume_pre_qty :=L_PRE_PERIOD_QTY;
----这时候已经用完红色沙子了，开始用黑色沙子了-----
---2.2 当黑色沙子数量大于瓶子 可用的容量 的时候。
IF L_CURR_PERIOD_QTY > (REC_SHIPMENT_LINE.PRIMARY_QUANTITY - L_PRE_PERIOD_QTY) THEN
---该瓶子 装黑色沙子的数量 就是 瓶子的可用容量。
L_shipment_consume_Tbl(N).consume_curr_qty := REC_SHIPMENT_LINE.PRIMARY_QUANTITY - L_PRE_PERIOD_QTY;
---本次还有多少数量需要被下一个单号分摊，就是确定剩下还有多少黑色沙子可用。
L_CURR_PERIOD_QTY := L_CURR_PERIOD_QTY - L_shipment_consume_Tbl(N).consume_curr_qty;
ELSE
----当黑色沙子数量 小于或者等于 瓶子的可用容量的时候
-----该瓶子装黑色沙子的数量就是 瓶子的可用容量。
L_shipment_consume_Tbl(N).consume_curr_qty := L_CURR_PERIOD_QTY;
-----黑色沙子用完咯！~~一定要赋值0，因为根据黑色沙子的使用情况判断后面是否要退出瓶子的循环。
L_CURR_PERIOD_QTY := 0;
END IF;
L_PRE_PERIOD_QTY:= 0;
END IF;
N := N+1;
----当黑色沙子用完的时候，要退出循环。因为沙子数量可能很少，但是瓶子很多。。。没必要再循环下去了。
IF L_CURR_PERIOD_QTY <= 0 THEN
EXIT;
END IF;
END LOOP;
---显示装的结果。
FOR I IN 1..L_shipment_consume_Tbl.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(L_shipment_consume_Tbl(I).SHIPMENT_LINE_ID
||'-'|| L_shipment_consume_Tbl(I).PRIMARY_QUANTITY
||'-'|| L_shipment_consume_Tbl(I).consume_pre_qty
||'-'|| L_shipment_consume_Tbl(I).consume_curr_qty
);
END LOOP;
END;
/*
---例如：
L_PRE_PERIOD_QTY:0 -L_CURR_PERIOD_QTY:2020.2
18467366-1605.5-0-1605.5
18633076-5014.7-0-414.7
*/
热心网友
时间：2022-04-13 08:46
慢是因为你两个表都是全表查询，事例中并没有看出任何关联。
如果你两个表一个一万数据，另外一个三万数据，这样不就是有三亿条笛卡儿积？
3亿条数据你觉得人类真的用肉眼去看？
正是因为你数据行数多才导致存储空间过大。
所以前提是主表必须where条件进行筛选。否则没意义。追问的确是要笛卡尔积，但是上面这个例子是已经省略了关联条件的一组例子，每一组都要进行这样的顺序分摊，而且分摊的结果为0的行可以去掉，分摊出来的结果不是全部给人看的，可以用于汇总或者针对指定的几个条件提取对应的几组数据来进行即时分摊查询
热心网友
时间：2022-04-13 10:04
