The oracle database diminishing SLQ for help-CodePudding

Red is the need to achieve the effect of decreasing

CodePudding user response:

 

SQL> Create table test (id int, num int, num2 int); 
The Table created 
SQL> The begin 
2 insert into the test values (1238, 0200); 
3 the insert into the test values (2238, 0600); 
4 the insert into the test values (3238, 0600); 
5 the end; 
6/
PL/SQL procedure successfully completed 
SQL> Select id, num, num2, num - sum (num2) over (order by id) result from the 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>

CodePudding user response:

 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 and c2, case when lag (c3) over (order by an rn) is null then c3 else lag (c3) over (order by an rn) | | '-' | | c2 end c4 
The from (select c1 and c2, c1 | | '-' | | c2 c3, rownum rn from t)

CodePudding user response:

As with t (
Select id 1, 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.