递归PL SQL查询帮助 [英] Recursive PL SQL Query Help
问题描述
我有两个桌子.我想从这两个表中得到以下结果.任何帮助表示赞赏.
I have a two table. I want to get the below result out from these two table. Any help appreciate.
事件表
event_id | gross_amount | transaction_id
1 | 10 | 1
2 | 12 | 5
交易表
TRANSACTION table
trx_id | debit | credit | link_trx_id
1 | 4 | 0 | null
2 | 0 | 2 | 1
3 | 0 | 1 | 2
4 | 3 | 0 | 3
5 | 0 | 5 | null
6 | 0 | 3 | 5
预期结果:
RESULT EXPECTED:
trx_id | debit | credit | current_gross | current_net
1 | 4 | 0 | 10 | 6
2 | 0 | 2 | 6 | 8
3 | 0 | 1 | 8 | 9
4 | 3 | 0 | 9 | 6
5 | 0 | 5 | 10 | 15
6 | 0 | 3 | 15 | 18
说明
您会看到事务1,2,3,4属于一个集合,而4,6属于另一个集合.对于每个需要的交易,它会将以前的交易的current_net值链接为current_gross.
Explanation
As you can see transaction 1,2,3,4 falling into an one set while 4,6 falling into an another set. For the each transaction needed the it's linked previous transactions current_net value as a it's current_gross.
基本上得到current_gross是一个递归调用.在这里,我不能使用PL SQL函数,而可以在其中编写快速递归函数来计算current_gross.我需要完成此任务的纯PL/SQL查询. (可以使用内置的PL SQL函数)
Basically getting current_gross is a recursive call. Here I can not use PL SQL function where I can write a quick recursive function to calculate current_gross. I need pure PL/SQL query for this task. ( Can use built in PL SQL functions)
推荐答案
使用Allan的查询,我添加了create表并进行了插入. 该查询的变量不匹配,因此我也进行了更正(debit_cum/credit_cum与子查询中的cum-credit/cum_debit变量不匹配).
Using Allan's query, I added the create table and inserts. The query had mismatched variables so I correct those as well (debit_cum/credit_cum did not match the cum-credit/cum_debit variables in the sub-query).
create table event
(event_id number(9),
gross_amount number(9),
transaction_id number(9));
insert into event values (1,10,1);
insert into event values (2,12,5);
create table transaction
(trx_id number(9),
debit number(9),
credit number(9),
link_trx_id number(9)
);
insert into transaction values (1,4,0,null);
insert into transaction values (2,0,2,1);
insert into transaction values (3,0,1,2);
insert into transaction values (4,3,0,3);
insert into transaction values (5,0,5,null);
insert into transaction values (6,0,3,5);
SELECT trx_id,
debit,
credit,
root_amt - debit_cum + credit_cum + debit - credit AS current_gross,
root_amt - debit_cum + credit_cum AS current_net
FROM (SELECT trx_id,
debit,
credit,
SUM(credit) OVER (PARTITION BY event_id ORDER BY lvl) AS credit_cum,
SUM(debit) OVER (PARTITION BY event_id ORDER BY lvl) AS debit_cum,
root_amt,
event_id
FROM (SELECT trx_id,
debit,
credit,
LEVEL AS lvl,
CONNECT_BY_ROOT (gross_amount) AS root_amt,
CONNECT_BY_ROOT (event_id) AS event_id
FROM transaction t LEFT OUTER JOIN event e ON t.trx_id = e.transaction_id
CONNECT BY link_trx_id = PRIOR trx_id
START WITH link_trx_id IS NULL));
这篇关于递归PL SQL查询帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!