oracle SQL - 在嵌套选择中使用值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6139449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - using a value in a nested select
提问by Nick
Hope the title makes some kind of sense - I'd basically like to do a nested select, based on a value in the original select, like so:
希望标题有某种意义 - 我基本上想根据原始选择中的值进行嵌套选择，如下所示：
SELECT MAX(iteration) AS maxiteration,
(SELECT column
FROM table
WHERE id = 223652
AND iteration = maxiteration)
FROM table
WHERE id = 223652;
I get an ORA-00904 invalid identifier error.
我收到 ORA-00904 无效标识符错误。
Would really appreciate any advice on how to return this value, thanks!
非常感谢有关如何返回此值的任何建议，谢谢！
采纳答案by Denis de Bernardy
It looks like this should be rewritten with a where clause:
看起来这应该用 where 子句重写：
select iteration,
col
from tbl
where id = 223652
and iteration = (select max(iteration) from tbl where id = 223652);
回答by Frank Schmitt
Since you're using Oracle, I'd suggest using analytic functions for this:
由于您使用的是 Oracle，我建议为此使用分析函数：
SELECT * FROM (
SELECT col,
iteration,
row_number() over (partition by id order by iteration desc) rn
FROM tab
WHERE id = 223652
) WHERE rn = 1
回答by Lieven Keersmaekers
You can circumvent the problem alltogether by placing the subselect in an INNER JOINof its own.
您可以通过将子选择放在INNER JOIN它自己的一个中来完全规避这个问题。
SELECT t.iteration
, t.column
FROM table t
INNER JOIN (
SELECT id, MAX(iteration) AS iteration
FROM table
WHERE id = 223652
) tm ON tm.id = t.id AND tm.iteration = t.iteration
回答by Daniel Hilgarth
do it like this:
像这样做：
with maxiteration as
(
SELECT MAX(iteration) AS maxiteration
FROM table
WHERE id = 223652
)
select
column,
iteration
from
table
where
id = 223652
AND iteration = maxiteration
;
回答by Rob
Not 100% sure on Oracle syntax, but isn't it something like:
不是 100% 确定 Oracle 语法，但它是不是像：
select iteration, column from table where id = 223652 order by iteration desc limit 1
回答by Seth Robertson
You do something like
你做类似的事情
select maxiteration,column from table a join (select max(iteration) as maxiteration from table where id=1) b using (id) where b.maxiteration=a.iteration;
This could of course return multiple rows for one maxiteration unless your table has a constraint against it.
这当然可以为一次 maxiteration 返回多行，除非您的表对其有约束。
回答by Tom H
I would approach this problem in a slightly different way. You're basically looking for the row that has no other iterations greater than it. There are at least 3 ways I can think of to do this:
我会以稍微不同的方式解决这个问题。您基本上是在寻找没有其他迭代大于它的行。我至少可以想到 3 种方法来做到这一点：
SELECT
T1.iteration AS maxiteration,
T1.column
FROM
Table T1
WHERE
T1.id = 223652 AND
NOT EXISTS
(
SELECT *
FROM Table T2
WHERE
T2.id = 223652 AND
T2.iteration > T1.iteration
)
Or...
或者...
SELECT
T1.iteration AS maxiteration,
T1.column
FROM
Table T1
LEFT OUTER JOIN Table T2 ON
T2.id = T1.id AND
T2.iteration > T1.iteration
WHERE
T1.id = 223652 AND
T2.id IS NULL
Or...
或者...
SELECT
T1.iteration AS maxiteration,
T1.column
FROM
Table T1
INNER JOIN (SELECT id, MAX(iteration) AS maxiteration FROM Table T2 GROUP BY id) SQ ON
SQ.id = T1.id AND
SQ.maxiteration = T1.iteration
WHERE
T1.id = 223652
EDIT:I didn't see the ORA error the first time reading the question and it wasn't tagged as Oracle specific. I think that there may be some differences in the syntax and use of aliases in Oracle, so you may need to tweak some of the above queries.
编辑：我第一次阅读问题时没有看到 ORA 错误，并且它没有被标记为特定于 Oracle 的。我认为可能在 Oracle 中在语法和别名的使用方面存在一些差异，因此您可能需要调整上述某些查询。
The Oracle error is telling you that it doesn't know what maxiteration is, because the column alias isn't available yet inside the subquery. You need to refer to it by the table alias and column name instead of the column alias I believe.
Oracle 错误告诉您它不知道最大迭代是什么，因为列别名在子查询中尚不可用。您需要通过表别名和列名而不是我相信的列别名来引用它。
