每次往卡里冲钱时,会记录每次存钱的记录,再取钱的时候会根据存钱的时间先后排序,即先存的钱先出.有一个账户表t_subacc,会记录每次充值的金额,时间..请问用SQL怎么实现,有小数.
t_account
id chargeCash date
1 10000 2015-09-15 14:29:37
2 11000 2015-09-17 14:29:37
3 15000 2015-09-19 14:29:37
4 20000 2015-09-21 14:29:37
5 30000 2015-09-24 14:29:37
现在要取出5W,按照时间的先后顺序取出.
------解决思路----------------------
噢，上面的那个是假设ID的顺序和时间的顺序一样的。
还是用下面这个吧，判断时间的。
with table1 as
(
select 1 id, 10000 chargeCash, to_date('2015-09-15 14:29:37', 'yyyy-mm-dd HH24:mi:ss') "date" from dual union all
select 2 id, 11000 chargeCash, to_date('2015-09-17 14:29:37', 'yyyy-mm-dd HH24:mi:ss') "date" from dual union all
select 3 id, 15000 chargeCash, to_date('2015-09-19 14:29:37', 'yyyy-mm-dd HH24:mi:ss') "date" from dual union all
select 4 id, 20000 chargeCash, to_date('2015-09-21 14:29:37', 'yyyy-mm-dd HH24:mi:ss') "date" from dual union all
select 5 id, 30000 chargeCash, to_date('2015-09-24 14:29:37', 'yyyy-mm-dd HH24:mi:ss') "date" from dual
)
select id, chargeCash, "date", id1, id2
, case when id2<50000 then 0 when id2>50000 and id1<50000 then id2-50000 else chargeCash end chargeCash1
from (
select id, chargeCash, "date"
, (select sum(chargeCash) from table1 b where a."date">b."date") id1
, (select sum(chargeCash) from table1 c where a."date">=c."date") id2
from table1 a
) d