表TT
name price fromdate todateA 2 2008-02-03 2008-03-02
A 3 2008-03-02 2008-04-01
A 2008-04-01 2008-05-05
A 2008-05-05 2009-01-01 A 6 2009-01-01
B 4 2008-02-08 2008-03-23 B 5 2008-03-23 要求查询:2008-06-26的价格(price)
fromdate表示开始时间,todate表示结束时间,todate为空的表示现在正在执行的价格。
如果在这段时间内,价格为空,则取离它时间最近的价格。因此结果为name price A 3 B 5
name price fromdate todateA 2 2008-02-03 2008-03-02
A 3 2008-03-02 2008-04-01
A 2008-04-01 2008-05-05
A 2008-05-05 2009-01-01 A 6 2009-01-01
B 4 2008-02-08 2008-03-23 B 5 2008-03-23 要求查询:2008-06-26的价格(price)
fromdate表示开始时间,todate表示结束时间,todate为空的表示现在正在执行的价格。
如果在这段时间内,价格为空,则取离它时间最近的价格。因此结果为name price A 3 B 5
错了吧.按你的规则
要不是
name price A 6 B 5
from( select name, price, row_number() over(partition by name order by to_date('20080626','yyyymmdd') - nvl(todate,fromdate)) rn
from t where price is not null and fromdate < to_date('20080626','yyyymmdd')
)
where rn = 1;
要求查询:2008-06-26的价格(price)
select name, price
from( select name, price, row_number() over(partition by name order by to_date('20080626','yyyymmdd') - nvl(todate,fromdate+0.01)) rn
from t where price is not null and fromdate < to_date('20080626','yyyymmdd')
)
where rn = 1;
from( select name, price, row_number() over(partition by name order by to_date('20080626','yyyymmdd') - nvl(todate,sysdate)) rn
from t where price is not null
)
where rn = 1;
where price is not null and
(fromdate<=date'2008-06-26' and todate>date'2008-06-26'
or not exists(
select 1 from tt
where price is not null and name=a.name
and case when todate<date'2008-06-26' then date'2008-06-26'-todate else fromdate-date'2008-6-26' end<case when a.todate<date'2008-06-26' then date'2008-06-26'-a.todate else a.fromdate-date'2008-06-26' end)
)