开发者

Oracle/Sql query for some calculation!

开发者 https://www.devze.com 2023-03-23 13:32 出处:网络
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,

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

精彩评论

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

关注公众号