SQL> WITH t(credit,积分,到期时间,姓名)AS
2 (SELECT 142,50,'20170912','haha' FROM dual
3 UNION ALL
4 SELECT 142,49,'20171012','haha' FROM dual
5 UNION ALL
6 SELECT 142,30,'20171112','haha' FROM dual
7 UNION ALL
8 SELECT 142,45,'20171212','haha' FROM dual)
9 SELECT least(greatest(SUM(积分)OVER(PARTITION BY 姓名 ORDER BY 到期时间)-credit,0),积分),
10 到期时间,
11 姓名 FROM T;
12 /
LEAST(GREATEST(SUM(积分)OVER(P 到期时间 姓名
------------------------------ -------- ----
0 20170912 haha
0 20171012 haha
0 20171112 haha
32 20171212 haha
--自己再优化下吧
你应该需要保留的是在有效期内的积分剩余吧,那你可以这样写sql
select * from (select 到期时间,
积分,(sum(积分) over(order by 到期时间))-142 val,姓名
from t where t.姓名='haha') a where a.val>0;
应该符合你的要求