with t1 as (select 'zhangsan' nm, 1000 init_amt from dual union all select 'lisi' nm, 2000 init_amt from dual), t2 as (select '20150301' dt, 'zhangsan' nm, 100 out_amt, 0 in_amt from dual union all select '20150302' dt, 'zhangsan' nm, 400 out_amt, 0 in_amt from dual union all select '20150303' dt, 'zhangsan' nm, 0 out_amt, 200 in_amt from dual union all select '20150301' dt, 'lisi' nm, 0 out_amt, 200 in_amt from dual union all select '20150301' dt, 'lisi' nm, 100 out_amt, 0 in_amt from dual) select t4.dt, t4.nm, t4.amt + t1.init_amt from t1, (select t3.dt, t3.nm, sum(out_amt * -1) over(partition by nm order by rn) + sum(in_amt) over(partition by nm order by rn) amt, rn from (select t2.*, rownum rn from t2) t3) t4 where t1.nm = t4.nm order by rn;
with t1 as
(select 'zhangsan' nm, 1000 init_amt
from dual
union all
select 'lisi' nm, 2000 init_amt
from dual),
t2 as
(select '20150301' dt, 'zhangsan' nm, 100 out_amt, 0 in_amt
from dual
union all
select '20150302' dt, 'zhangsan' nm, 400 out_amt, 0 in_amt
from dual
union all
select '20150303' dt, 'zhangsan' nm, 0 out_amt, 200 in_amt
from dual
union all
select '20150301' dt, 'lisi' nm, 0 out_amt, 200 in_amt
from dual
union all
select '20150301' dt, 'lisi' nm, 100 out_amt, 0 in_amt
from dual)
select t4.dt, t4.nm, t4.amt + t1.init_amt
from t1,
(select t3.dt,
t3.nm,
sum(out_amt * -1) over(partition by nm order by rn) + sum(in_amt) over(partition by nm order by rn) amt,
rn
from (select t2.*, rownum rn from t2) t3) t4
where t1.nm = t4.nm
order by rn;