oracle 子查询的解决方法~

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;