使用标量子查询创建累计和

create view v(id, amt, trx)
as
select 1, 100, 'PR' from dual union all
select 2, 100, 'PR' from dual union all
select 3, 50, 'PY' from dual union all
select 4, 100, 'PR' from dual union all
select 5, 50, 'PY' from dual;

select case when v1.trx = 'PY' then 'payment' else 'purchase' end as trx_type,
v1.amt,
(select sum(case when v2.trx = 'PY' then -v2.amt else v2.amt end) from v v2 where v2.id <= v1.id) as blance 
from v v1;

TRX_TYPE AMT BLANCE
-------- ---------- ----------
purchase 100 100
purchase 100 200
payment 50 150
purchase 100 250
payment 50 200