有如下几条记录,记录同一个商品由不同供应商在不同时期的进货价格,DATE字段代表的是进货价格起始日:table结构如下:
date price vendor
20100101 10.00 A
20100321 12.00 A
20100425 9.00 A
20100209 10.00 B今天是4月2日,我如何能取到目前各个供应商的进货价格呢?
20100209 10.00 B
20100321 12.00 A谢谢各位啦~~
date price vendor
20100101 10.00 A
20100321 12.00 A
20100425 9.00 A
20100209 10.00 B今天是4月2日,我如何能取到目前各个供应商的进货价格呢?
20100209 10.00 B
20100321 12.00 A谢谢各位啦~~
from (
select t.*, row_number() over(partition by vendor order by date desc) rn from t where date < sysdate
)
where rn = 1;
where not exists(
select 1 from TABNAME where vender=t.vender and date<=sysdate and date>t.date)
from (
select t.*, row_number() over(partition by vendor order by "date" desc) rn from t where "date"< sysdate
)
where rn = 1;