不好意思,又来提问:有如下几条记录,记录同一个商品由不同供应商在不同时期的多个币种的进货价格,DATE字段代表的是进货价格起始日:table结构如下:
date price vendor 币种
20100101 10.00 A USD
20100321 12.00 A CNY
20100425 9.00 A CNY
20100209 10.00 B CNY
20100301 2.01 B USD今天是4月2日,我如何能取到目前各个供应商的的不同币种的进货价格呢?
20100209 10.00 B
20100321 12.00 A CNY
20100101 10.00 A USD
20100209 10.00 B CNY
20100301 2.01 B USD谢谢各位啦~~
date price vendor 币种
20100101 10.00 A USD
20100321 12.00 A CNY
20100425 9.00 A CNY
20100209 10.00 B CNY
20100301 2.01 B USD今天是4月2日,我如何能取到目前各个供应商的的不同币种的进货价格呢?
20100209 10.00 B
20100321 12.00 A CNY
20100101 10.00 A USD
20100209 10.00 B CNY
20100301 2.01 B USD谢谢各位啦~~
select * from tablename where date=to_date('20100402','YYYYMMDD');
with test as(
select date'2010-01-01' s_date, 10.00 price,'A' vendor, 'USD' m_kind from dual union
select date'2010-03-21' s_date, 12.00 price,'A' vendor, 'CNY' m_kind from dual union
select date'2010-04-25' s_date, 9.00 price,'A' vendor, 'CNY' m_kind from dual union
select date'2010-02-09' s_date, 10.00 price,'B' vendor, 'CNY' m_kind from dual union
select date'2010-03-01' s_date, 2.01 price,'B' vendor, 'USD' m_kind from dual)
SELECT s_date, price, vendor, m_kind
FROM (SELECT t.*,
rank() over(PARTITION BY vendor, m_kind ORDER BY s_date DESC) rn
FROM test t)
WHERE rn = 1;
使用可参考:
http://hzhlde.blog.163.com/blog/static/106726465200961610248234/
where to_date(date,'yyyy-mm-dd') in (select date from (select max(to_date(date,'yyyy-mm-dd')) date,vendor,币种 from table) a) and to_date(date,'yyyy-mm-dd')<='2010-04-02'
select date,price,vendor,币种 from (select date,price,vendor,币种,row_number() over(partition by vendor,币种 order by date desc) rn
from table
where to_date(date,'yyyy-mm-dd')<='2010-04-02') a
where rn=1
也可以这样 好理解些 上面的不是很好看 明了