我有传入表事务
表名：WA_II_TBL_TR_EQUIPMENT
TRANSACTION_EQUIPMENTID | DESCRIPTION | SUPPLIERID_FK | PART_NO | QUANTITY
TE201708000002 | fg | 62551 | GSDFGSG | 2
TE201709000003 | fg | 62551 | PA000535| 7
TE201709000002 | fg | 62551 | GSDFGSG | 9
TE201708000004 | fg | 62551 | GSDFGSG | 10
TE201708000003 | fg | 62551 | GSDFGSG | 2
TE201709000001 | Test | 48544 | Test | 8
在上表中，我们可以看到 3PART_NO
现在我需要获取第一个TRANSACTION_EQUIPMENTID和分组，以获得基于PART_NO的SUM(QUANTITY)，如下所示：
TRANSACTION_EQUIPMENTID | PART_NO | QUANTITY
TE201708000002 | GSDFGSG | 23
TE201709000003 | PA000535| 7
TE201709000001 | Test | 8
查询：
SELECT
MIN(TRANSACTION_EQUIPMENTID),
PART_NO,
SUM(T.QUANTITY) AS TOTAL
FROM
WA_II_TBL_TR_EQUIPMENT T
WHERE
T.SUPPLIERID_FK = '62551'
GROUP BY
T.PART_NO
正如你现在看到的，我可以得到第一个TRANSACTION_EQUIPMENTID，这TE201708000002
现在我有传出表事务
表名：WA_II_TBL_TR_OUT_EQUIPMENT
TRANSACTION_OUT_EQUIPMENTID | TRANSACTION_EQUIPMENTID_FK | QUANTITY <--QUANTITY OUT
TOE201709000001 | TE201708000002 | 3
假设我有PART_NO=GSDFGSG的传出事务，输出数量 = 3。(第一个 ID：已插入TE201708000002。
现在我希望在查询时根据表WA_II_TBL_TR_OUT_EQUIPMENT获取表WA_II_TBL_TR_EQUIPMENT的第一个TRANSACTION_EQUIPMENTID。
TRANSACTION_EQUIPMENTIDTE201708000002已消耗，数量为 3。现在它应该是下一个TRANSACTION_EQUIPMENTIDTE201708000003，因为它仍然有这些 ID 的数量休息 1。
如何根据消耗WA_II_TBL_TR_OUT_EQUIPMENT获得第一TRANSACTION_EQUIPMENTID？
在这种情况下应该是这样的：
TRANSACTION_EQUIPMENTID | PART_NO | QUANTITY
TE201708000003 | GSDFGSG | 20 (Rest Quantity, before 23)
TE201709000003 | PA000535| 7
以下新案例
在表WA_II_TBL_TR_OUT_EQUIPMENT上，我有以下数据：
TRANSACTION_EQUIPMENTID_FK | QUANTITY
TE201708000002 | 3
TE201708000003 | 1
当我查询代码时
ID | PART_NO | SEI_NO | REST
TE201708000003 | GSDFGSG | SDGDFGSD | 1 <-- it should be 0
查询：
select id, part_no, sei_no, sq2 - nvl(oq2, 0) rest
from (
select tr.*, row_number() over (partition by part_no, sei_no order by id) rnk
from (select i.transaction_equipmentid id, part_no, sei_no, i.quantity iq,
sum(i.quantity) over (partition by part_no,sei_no
order by i.transaction_equipmentid) sq1,
sum(i.quantity) over (partition by part_no, sei_no) sq2,
o.quantity oq1,
max(o.quantity) over (partition by part_no, sei_no) oq2
from wa_ii_tbl_tr_equipment i
left join wa_ii_tbl_tr_out_equipment o
on o.transaction_equipmentid_fk = i.transaction_equipmentid
where i.supplierid_fk = 62551 ) tr
where sq1 >= oq2 or oq2 is null )
where rnk = 1;
select id, part_no, sq2-oq2 rest
from (
select tr.*, row_number() over (partition by part_no order by id) rnk
from (select i.transaction_equipmentid id, part_no, i.quantity iq,
sum(i.quantity) over (partition by part_no
order by i.transaction_equipmentid) sq1,
sum(i.quantity) over (partition by part_no) sq2,
o.quantity oq1,
max(o.quantity) over (partition by part_no) oq2
from wa_ii_tbl_tr_equipment i
left join wa_ii_tbl_tr_out_equipment o
on o.transaction_equipmentid_fk = i.transaction_equipmentid
where i.supplierid_fk = 62551 ) tr
where sq1 >= oq2 or oq2 is null )
where rnk = 1
我连接输入和输出，然后使用分析函数sum()，max()我准备了所有需要的信息，最重要的是累积总和。您可以看到它单独运行最内部的查询，别名为tr。
下一步是仅添加row_number()以查找输入大于输出的第一行，并显示此行和剩余差异。
此条件：还需要or oq2 is null来显示其他部分(此处PA000535)。
请阅读有关分析函数的信息，网上有很多教程。
测试数据：
create table WA_II_TBL_TR_EQUIPMENT(
TRANSACTION_EQUIPMENTID varchar2(15), DESCRIPTION varchar2(10),
SUPPLIERID_FK number(6), PART_NO varchar2(10), QUANTITY number(6));
insert into WA_II_TBL_TR_EQUIPMENT values ('TE201708000002', 'fg', 62551, 'GSDFGSG', 2);
insert into WA_II_TBL_TR_EQUIPMENT values ('TE201709000003', 'fg', 62551, 'PA000535', 7);
insert into WA_II_TBL_TR_EQUIPMENT values ('TE201709000002', 'fg', 62551, 'GSDFGSG', 9);
insert into WA_II_TBL_TR_EQUIPMENT values ('TE201708000004', 'fg', 62551, 'GSDFGSG', 10);
insert into WA_II_TBL_TR_EQUIPMENT values ('TE201708000003', 'fg', 62551, 'GSDFGSG', 2);
insert into WA_II_TBL_TR_EQUIPMENT values ('TE201709000001', 'Test', 48544, 'Test', 8);
create table WA_II_TBL_TR_OUT_EQUIPMENT(
TRANSACTION_OUT_EQUIPMENTID varchar2(15),
TRANSACTION_EQUIPMENTID_FK varchar2(15),
QUANTITY number(6));
insert into WA_II_TBL_TR_OUT_EQUIPMENT values('TOE201709000001', 'TE201708000002', 3);
输出：
ID PART_NO REST
--------------- ---------- ----------
TE201708000003 GSDFGSG 20
TE201709000003 PA000535