代码测试如下:
SQL> select * from test_t;
ITEM_CODE BALANCE_DATE QTY
-------------------- ------------ ----------
AAAA 2017/5/7 70000
AAAA 2017/5/22 5000
AAAA 2017/5/29 10000
AAAA 2017/6/12 6000
SQL>
SQL> select item_code,
2 balance_date,
3 qty,
4 case
5 when sum(qty) over(partition by item_code order by balance_date) <= 80000 then
6 0
7 else
8 case
9 when sum(qty) over(partition by item_code order by balance_date) - qty <= 80000 then
10 sum(qty) over(partition by item_code order by balance_date) - 80000
11 else
12 qty
13 end
14 end qty2
15 from test_t
16 ;
ITEM_CODE BALANCE_DATE QTY QTY2
-------------------- ------------ ---------- ----------
AAAA 2017/5/7 70000 0
AAAA 2017/5/22 5000 0
AAAA 2017/5/29 10000 5000
AAAA 2017/6/12 6000 6000
SQL>