开发者

Oracle/Sql query for some calculation!

I want to make one question about oracle/sql query.

I have some data like below,

   Column1
     25
     20
     15
     12
     11
     10

I want to get result like this,

   Column1
     5        (25-20)
     5        (20-开发者_运维知识库15)   
     3        (15-12) 
     1        (12-11)
     1        (11-10)

I am using cursor to get those results. But, I don't really want to use cursor, because I have so many rows to calculate. Is there any function to get that results in Oracle/Sql like "Case When,Roll Up".


These functions are lag() and lead(), see here - http://www.orafaq.com/node/55

They can be used like this -

select 
  value - prev_value as diff,
  '(' || to_char(value) || ' - ' || to_char(prev_value) || ')' as expression
from (  
select value, idx, 
  lag(value) over (order by idx) as next_value,
  lead(value) over (order by idx) as prev_value
from(
          select 25 as value, 1 as idx from dual
union all select 20 as value, 2 as idx from dual
union all select 15 as value, 3 as idx from dual
union all select 12 as value, 4 as idx from dual
union all select 11 as value, 5 as idx from dual
union all select 10 as value, 6 as idx from dual
)
) where prev_value is not null
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜