SQL oracle中的子选择

Sub-select in oracle

提问by jgauffin

I'm trying to select the newest price from another table in a sub select. But I can't figure out how to get it working.

我正在尝试从子选择中的另一个表中选择最新价格。但我不知道如何让它工作。

This is what I've tried:

这是我尝试过的：

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

The subselect works by itself, but it can't find article_table.part_no:

子选择自行工作，但找不到article_table.part_no：

SQL Error: ORA-00904: "article_table "."part_no": invalid identifier

SQL 错误：ORA-00904：“article_table”.“part_no”：标识符无效

Update:

更新：

Current solution:

当前解决方案：

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

回答by Vincent Malgrat

In Oracle, subqueries can only see values from parent queries one level deep. Since you have two nested selects the inner one can't see the values from the outer one.

在 Oracle 中，子查询只能查看一级深度的父查询的值。由于您有两个嵌套选择，因此内部选择无法看到外部选择的值。

You could perform the join first:

您可以先执行连接：

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;

You could also use a PL/SQL function that would return the first quote_pricefrom price_historywhen given a article_table.part_no.

你也可以使用一个PL / SQL函数会返回前quote_price从price_history时提供的article_table.part_no。

回答by Lukas Eder

Try aliasing article_tablein the outermost query:

尝试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

Besides, you might want to look into Oracle analytic functions to make simpler queries for that kind of purpose:

此外，您可能希望查看 Oracle 分析函数以针对此类目的进行更简单的查询：

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

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

回答by Benoit

I would try the following:

我会尝试以下方法：

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;