Oracle/Sql 查询进行一些计算！

发布于 2024-11-26 22:15:51 字数 401 浏览 1 评论 0原文

我想问一个关于oracle/sql查询的问题。

我有一些如下数据，

我想得到这样的结果，

   Column1
     5        (25-20)
     5        (20-15)   
     3        (15-12) 
     1        (12-11)
     1        (11-10)

我正在使用光标来获取这些结果。但是，我真的不想使用游标，因为我有很多行要计算。是否有任何函数可以在 Oracle/Sql 中获得结果，例如“Case When,Roll Up”。

原文

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

I have some data like below,

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".

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伴我心暖 2024-12-03 22:15:51

这些函数是 lag() 和 Lead()，请参见此处 - http://www.orafaq.com/node /55

它们可以这样使用 -

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

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 原文

~没有更多了~