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
精彩评论