select something, somthingelse,
(
select * from
(
select QUOTE_PRICE as old_price
from price_history
where price_history.part_no= article_table.part_no
order by valid_from desc
) where rownum=1
)
from article_table where rownum < 5
这里是会报:SQL Error: ORA-00904: “article_table “.”part_no”: invalid identifier
在oracle中,子查询只能嵌套两层,否则就不能识别到第三层的就不能识别到第一层的table。
这里我介绍一下一种方法可以解决上面所述方法:
SELECT something, somthingelse, old_price
FROM (SELECT a.something, a.somthingelse, p.quote_price old_price,
row_number() over (PARTITION BY a.part_no
ORDER BY valid_from DESC) rnk
FROM article_table a
LEFT JOIN price_history p ON a.part_no = p.part_no)
WHERE rnk = 1;