oracle数据库slq递减求助

[ZuoChengKeJiYunXua]

红色是需要达到的 效果 递减

[卖水果的net]

SQL> create table test(id int, num int, num2 int);
Table created
SQL> begin
  2      insert into test values(1,2380,2000);
  3      insert into test values(2,2380,600);
  4      insert into test values(3,2380,600);
  5  end;
  6  /
PL/SQL procedure successfully completed
SQL> select id, num, num2, num - sum(num2) over(order by id) result from test ;
        ID        NUM       NUM2     RESULT
---------- ---------- ---------- ----------
         1       2380       2000        380
         2       2380        600       -220
         3       2380        600       -820
SQL> drop table test purge ;
Table dropped

SQL> 

[ghx287524027]

with t as(
select 2380 as c1,2000 as c2 from dual union all
select 2380 as c1,600 as c2 from dual union all
select 2380 as c1,600 as c2 from dual 
)
select c1,c2,case when lag(c3)over(order by rn) is null then c3 else lag(c3)over(order by rn)||'-'||c2 end c4
from (select c1,c2,c1||'-'||c2 c3,rownum rn from t)

[mayanzs]

with t as( select 1 id,2380 as c1,2000 as c2 from dual union all select 2 id,2380 as c1,600 as c2 from dual union all select 3 id,2380 as c1,600 as c2 from dual ) select t.*,c1-sum(c2) over(order by id rows between unbounded preceding and current row) ss from t;