Oracle查询以根据消耗的传出事务获取第一个事务ID [英] Oracle query to get first transaction ID based on outgoing transaction consumed
问题描述
我有来料表交易
表名称: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
在上表中，我们可以看到3个PART_NO
On above table we can see 3 PART_NO
现在我需要先获取TRANSACTION_EQUIPMENTID并分组以基于PART_NO获得SUM(QUANTITY)，例如:
And now I need to get first TRANSACTION_EQUIPMENTID and grouping to get SUM(QUANTITY) based on PART_NO to be like:
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
As you can see now I can get first TRANSACTION_EQUIPMENTID, that's TE201708000002
现在我有待付款的表交易
And now I have Outgoing Table Transaction
表名称:WA_II_TBL_TR_OUT_EQUIPMENT
TRANSACTION_OUT_EQUIPMENTID | TRANSACTION_EQUIPMENTID_FK | QUANTITY <--QUANTITY OUT
TOE201709000001 | TE201708000002 | 3
假定我有PART_NO = GSDFGSG的外向交易，数量Out =3.(已插入第一个ID:TE201708000002.)
Assume I have outgoing transaction for PART_NO = GSDFGSG with Quantity Out = 3. (First ID: TE201708000002 has been inserted.)
现在我想在查询时基于表WA_II_TBL_TR_OUT_EQUIPMENT在表WA_II_TBL_TR_EQUIPMENT上获得第一个TRANSACTION_EQUIPMENTID.
And now I want when Querying to get first TRANSACTION_EQUIPMENTID on table WA_II_TBL_TR_EQUIPMENT based on table WA_II_TBL_TR_OUT_EQUIPMENT.
TRANSACTION_EQUIPMENTID TE201708000002已被消耗，数量为3.现在应该下一个TRANSACTION_EQUIPMENTID TE201708000003，因为这些ID的剩余数量为1.
TRANSACTION_EQUIPMENTID TE201708000002 has been consumed with quantity out 3. Now it should be get next TRANSACTION_EQUIPMENTID TE201708000003 because it still have quantity rest 1 for these ID.
如何根据消耗的WA_II_TBL_TR_OUT_EQUIPMENT首先获得TRANSACTION_EQUIPMENTID?
How to get first TRANSACTION_EQUIPMENTID based on WA_II_TBL_TR_OUT_EQUIPMENT consumed?
在这种情况下应该是这样的:
In this case should be like this:
TRANSACTION_EQUIPMENTID | PART_NO | QUANTITY
TE201708000003 | GSDFGSG | 20 (Rest Quantity, before 23)
TE201709000003 | PA000535| 7
下面的新案例
在表WA_II_TBL_TR_OUT_EQUIPMENT上，我有以下数据:
New case below
On table WA_II_TBL_TR_OUT_EQUIPMENT, I have this below data:
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.
I join input and output, then using analytic functions sum() and max() I prepare all needed informations, most important is cumulative sum. You can see it running the most inner query separately, this aliased as tr.
下一步是仅添加row_number()来查找输入大于输出的第一行，并显示此行和剩余的差异.
Next step is only add row_number() to find first row(s) where input is greater than output and show this row and remaining difference.
此情况:or oq2 is null还需要显示其他部分(此处为PA000535).
This condition: or oq2 is null is needed to show also other parts (here PA000535).
请阅读有关解析函数的信息，网络上有很多教程.
Please read about analytic functions, there are many tutorials on the net.
测试数据:
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
这篇关于Oracle查询以根据消耗的传出事务获取第一个事务ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
