学步园

with a as
(select 10 aa,1 bb from dual
union all
select 8,2 from dual
union all
select 10,3 from dual
union all
select 10,4 from dual
),
b as
(
  select 5 cc,1 dd from dual
)

select aa,bb,case when (dd<0 and ee is null) then cc  when dd>=0 then aa  when ee>=0 then ee else 0  end cc  from

(
    select aa,bb,cc,cc-ee dd,lag(cc-ee)over(partition by dd order by bb) ee
    from
        (
               select aa,bb,cc,dd,sum(aa)over(partition by dd order by bb) ee from a,b
        )
)

----分别测试： 9，17，18，25，28，29

 --都OK。

--最后一行就是你要的数据

--增加第四行测试也OK。