使用Allan的查询，我添加了create表并进行了插入。该查询的变量不匹配，因此我也进行了更正（debit_cum / credit_cum与子查询中的cum-credit / cum_debit变量不匹配）。
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));
602392714
清零编程群