Хитрый запрос

Привет всем!

вот у меня есть допусти два столбца А и Б, как получит В? Т.е. из А вычитам Б и на следующей строке уже остаток от А

А               Б           В  
100           30        100
100           20         70
100           40         50
100           10         10
100            5           0

Axids,

lag

Axids,

Лучше, сюда курить:

SQL> with t as (
  2  select 100 a, 30 b from dual union all
  3  select 100, 20 from dual union all
  4  select 100, 40 from dual union all
  5  select 100, 10 from dual union all
  6  select 100, 5 from dual
  7  )
  8  select a, b,
  9  max(a) over (order by null) - sum(b) over (order by null rows between unbounded preceding and current row)
 10  from (select t.*, rownum rn from t )
 11  /
 
         A          B MAX(A)OVER(ORDERBYNULL)-SUM(B)
---------- ---------- ------------------------------
       100         30                             70
       100         20                             50
       100         40                             10
       100         10                              0
       100          5                             -5
 
SQL> 

Axids,

with t as (select 100 A, 30 B from dual union all
select 100, 20 from dual union all
select 100, 40 from dual union all
select 100, 10 from dual union all
select 100, 5 from dual)

select a,b,c from t
model
dimension by (rownum rn )
measures (a, b, cast (null as number) c)
rules upsert (
c[rn] order by rn asc = coalesce(c[cv(rn)-1] - b[cv(rn)-1],a[cv(rn)])
)

A                      B                      C                      
---------------------- ---------------------- ---------------------- 
100                    30                     100                    
100                    20                     70                     
100                    40                     50                     
100                    10                     10                     
100                    5                      0                      

Elapsed: 00:00:00.000

ILoveCoffee,

70 должно быть остатком на следующей строке

Axids,

как минимум - не хватает условия сортировки

Axids,

SQL> with t as (
  2  select 100 a, 30 b from dual union all
  3  select 100, 20 from dual union all
  4  select 100, 40 from dual union all
  5  select 100, 10 from dual union all
  6  select 100, 5 from dual
  7  )
  8  select a, b,
  9  (a+b) - sum(b) over (order by null rows between unbounded preceding and current row)
 10  from (select t.*, rownum rn from t)
 11  /
 
         A          B (A+B)-SUM(B)OVER(ORDERBYNULLRO
---------- ---------- ------------------------------
       100         30                            100
       100         20                             70
       100         40                             50
       100         10                             10
       100          5                              0
 
SQL> 

А так-то все просто, без всяких хитростей:

with t as (select 100 A, 30 B from dual union all
select 100, 20 from dual union all
select 100, 40 from dual union all
select 100, 10 from dual union all
select 100, 5 from dual)
--
select t.*, 
       a - nvl(sum(b) over(order by rownum 
                               rows between unbounded preceding 
                                        and 1 preceding),0) as x
  from t

Спасибо очень очень помогли!

ILoveCoffee,

уж если заморачиваться с явным окном, то сразу правильно задавать его границы... ;)