Oracle多层子查询无法识别外层table

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。

• 解决方案： 按列分片，各自分片中各自计算row_number

-- 外层嵌套为获取每个分片第一条
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;