如何获取表中的下一个和下一个下一个日期的值 [英] 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:
-
具有有效日期的行中的CVal(NextVal)和CPrice(NextPrice)值在某个日期之后的下一个日期
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屋!