问题描述:
查询本月入库的材料单价时,同时查询去年最后一次入库的单价做为参考价格.请教大家怎么写下面的查询速度能快些,
select v1.mtrl_id,v1.price,v2.last_price from
(select mtrl_id,price from table1 where mnth='2009-01')v1 left join
(select mtrl_id,price as last_price from table1 where year='2008' and
date=(select max(date) from table1 where year='2008'))v2 on v2.mtrl_id=v1.mtrl_id现在这个查询速度极慢,我用存储过程来处理,怎么处理都行,建临时表或者其他办法都行谢谢各位了!
查询本月入库的材料单价时,同时查询去年最后一次入库的单价做为参考价格.请教大家怎么写下面的查询速度能快些,
select v1.mtrl_id,v1.price,v2.last_price from
(select mtrl_id,price from table1 where mnth='2009-01')v1 left join
(select mtrl_id,price as last_price from table1 where year='2008' and
date=(select max(date) from table1 where year='2008'))v2 on v2.mtrl_id=v1.mtrl_id现在这个查询速度极慢,我用存储过程来处理,怎么处理都行,建临时表或者其他办法都行谢谢各位了!
解决方案 »
- 如何获取发生异常的SQL语句?
- 中文显示乱码问题
- 使用dbms_job每年初执行一个存储过程时遇到的问题,求大神解决。
- 在线等答案:起ORACLE时报:ORA-01113: file 3 needs media recovery 错误 (小女子,请个位大哥,多多帮忙)
- 我的Oracle 登不上去报措
- ORACLE中UPDATE语句的问题
- oracle9i透明网关技术
- 100分!我在.net版块发的贴,关于在oracle数据库建视图的。急,在线等。
- 简单的问题:表中建立了主键是否等于同时建立了该字段的索引??
- 请教:存储过程如何与外部的应用程序进行消息的传递??
- 求助:create创建表格和字段时候能够加注释吗
- oracle 9i字符串解析
select mtrl_id,price,last_price from
TABLE1 A, (select max(date) DATE from table1 where year='2008')B
WHERE A.DATE=B.DATE
A.MNTH='2009-01'
from (select mtrl_id,price from table1 where mnth='2009-01')v1
left join
(select *
from (select T.*,rownumber() over(partition by mtrl_id,date order by date desc) rn from table1 T where year='2008')A
where rn=1
)v2
on v2.mtrl_id=v1.mtrl_id
select v1.mtrl_id, v1.price, v2.price last_price
from (select mtrl_id, price from table1 where mnth = '2009-01') v1,
(select mtrl_id, price
from (select mtrl_id,
date,
price,
rank() over(partition by mtrl_id, date order by date desc) rk
from table1
where year = '2008') A
where rk = 1) v2
where v1.mtrl_id = v2.mtrl_id(+)