在Oracle中的子查询

Question

我正在尝试从另一个表中的子查询中选择最新的价格。但是我无法弄清楚如何使其正常工作。

这是我尝试过的：

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

子查询单独运行时可以正常工作，但无法找到article_table.part_no字段:

SQL错误：ORA-00904: "article_table"."part_no"：无效标识符

更新：

当前解决方案：

select something, somethingelse, (
  SELECT MIN(QUOTE_PRICE) KEEP (DENSE_RANK FIRST ORDER BY valid_from)
  FROM price_history
  WHERE part_no=article_table.part_no
) as old_price
from  article_table a where rownum < 5

- jgauffin

Answer 1

在Oracle中，子查询只能查看来自上一级查询的值。由于您有两个嵌套的选择，内部选择无法查看外部选择的值。

您可以先执行连接操作:

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;

您也可以使用一个PL/SQL函数，当给定article_table.part_no时，它会从price_history返回第一个quote_price。

- Vincent Malgrat

---

无法工作。我不想只选择一行。我想获取 article_table 中所有行的上一个价格。或者也许我误解了你的建议？ - jgauffin

@jgauffin：运行查询 :) -- 查询将为每个article_table行返回一行。 - Vincent Malgrat

是的，谢谢。+1。您认为我在问题中添加的解决方案怎么样？ - jgauffin

@jgauffin：这是“一级深度”限制的一个不错的解决方法。它强制执行一种嵌套循环连接（概念上：获取一行，从另一个表中获取最大值，获取下一行...），因此如果您不从article_table中获取大量行，则执行速度非常快（在您的情况下，这可能是最佳方法，因为您仅返回5行）。对于大型数据集，在执行连接之前允许优化器从更大的访问路径集合中进行选择，这可能会导致更有效的计划（特别是哈希连接）。 - Vincent Malgrat

你是不是想用 first_value() 而不是 row_number() ... where = 1？ - Benoit

@Benoit: first_value 会针对 article_table 中的每一行返回 N 行结果（其中 N 是该 part_no 的 price_history 行数）。这 N 行结果中的每一行都将具有相同的 old_price，因此您需要以某种方式进行去重。您可以使用 DISTINCT 进行去重，但我认为 RANK 方法更易于阅读 - 我使用 row_number 是因为我不知道 price_history 中的 (part_no, valid_from) 是否唯一。 - Vincent Malgrat

Answer 2

尝试在最外层查询中给article_table起一个别名：

select a.something, a.somthingelse, 
  (
    select * from 
    (
      select QUOTE_PRICE as old_price
      from price_history
      where price_history.part_no= a.part_no
      order by valid_from desc
    ) where rownum=1
  )
from  article_table a where rownum < 5

此外，您可能希望了解Oracle分析函数，以便为这种目的制作更简单的查询：

http://psoug.org/reference/analytic_functions.html

- Lukas Eder

---

我请求恢复你的回答，因为链接到分析函数帮助我解决了我的更新答案中的问题。如果没有更好的解决方案，我会接受它的。不要那么快删除回答。 - jgauffin

随你所愿... :-) 我认为主要答案是错误的，分析函数也是由其他人提出的，所以我的答案对其他读者来说是误导性的... - Lukas Eder

Answer 3

我会尝试以下方法：

select something, somethingelse, last_value(quote_price) over (partition by part_no order by valid_from asc)
  from article_table inner join price_history using (part_no)
 where rownum < 5;

- Benoit