赞
踩
SQL> select * from test;
ID AMOUNT TYPE
---------- ---------- ----------
1 100 in
2 100 in
3 50 out
4 70 out
5 300 in
test是随便创建的一个实例表,其中id列唯一标识每次事务处理,amount列标识每次存取金额的数量,type代表了是存(in)还是取(out)。
SQL> select id,amount,case when type='in' then 'Purchase' else 'payment' end type,
2 sum(case when type='in' then amount else -amount end)
3 over(order by id) as balance
4 from test
5 ;
ID AMOUNT TYPE BALANCE
---------- ---------- -------- ----------
1 100 Purchase 100
2 100 Purchase 200
3 50 payment 150
4 70 payment 80
5 300 Purchase 380
balance列记录了每次存取操作以后的账户余额。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。