如何获取表中的下一个和下一个下一个日期的值 [英] How to get the values for the next and next next date in a table

问题描述

我们有一个包含有效日期，CVal，CPrice列的表

We have a table with columns Effective_Date, CVal,CPrice

我们如何查询表以返回1行中这2行的值:

How do we query the table to return values from these 2 rows in 1 row:

1. 具有有效日期的行中的CVal(NextVal)和CPrice(NextPrice)值在某个日期之后的下一个日期

1. CVal (NextVal) and CPrice (NextPrice) values from row with Effective_Date the next date after somedate

CVal(SecondVal)和CPrice(SecondPrice)值来自#1的有效日期之后的下一个日期

CVal (SecondVal) and CPrice (SecondPrice) values from row with Effective_Date the next date after the Effective_Date from #1

例如:

Effective_Date  CVal   CPrice
01-JAN-19       1       100
01-JAN-20       2       101
01-JAN-21       3       102
01-JAN-22       4       103

说somedate = '31 -DEC-19'

Say somedate = '31-DEC-19'

预期结果

(有效日期"列中"31-DEC-19"之后的下一个日期是20年1月1日，

(the next date after '31-DEC-19' in the Effective_Date column is 01-JAN-20,

，此后的下一个日期是21年1月1日):

and the next date after that is 01-JAN-21):

NextVal NextPrice SecondVal SecondPrice
2       101       3         102

谢谢.

使用zip中的答案进行编辑(将topnum替换为rownum = 1，因为top在我的Oracle上不起作用)

Edited with answer from zip (replaced "top" with where rownum = 1, since top doesn't work on my Oracle) :

  select t.* from
 (
  lead(CVal, 1) over(order by Effective_Date) as NextVal 
  ,lead(CPrice, 1) over(order by Effective_Date) as NextPrice  
  ,lead(CVal, 2) over(order by Effective_Date) as SecondVal 
  ,lead(CPrice, 2) over(order by Effective_Date) as SecondPrice
  from tbl where Effective_Date >=  '31-DEC-19'
  order by Effective_Date ) t
  where rownum = 1

推荐答案

您可以使用窗口功能

    select  
    lead(CVal, 1) over(order by Effective_Date) as NextVal 
    ,lead(CPrice, 1) over(order by Effective_Date) as NextPrice  
    ,lead(CVal, 2) over(order by Effective_Date) as SecondVal 
    ,lead(CPrice, 2) over(order by Effective_Date) as SecondPrice

    from tbl where Effective_Date >=  '31-DEC-19'
    where rownum = 1
    order by Effective_Date 

输出为

NextVal NextPrice SecondVal SecondPrice
2       101       3         102

这篇关于如何获取表中的下一个和下一个下一个日期的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！