SQL Oracle 子查询没有看到来自外部块 2 级别的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2048244/
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
Oracle subquery does not see the variable from the outer block 2 levels up
提问by user248789
I'd like to get in one query a post and the first comment associated with the post. Here is how I do it in PostgreSQL:
我想在一个查询中获得一个帖子和与该帖子相关的第一条评论。以下是我在 PostgreSQL 中的做法：
SELECT p.post_id,
(select * from
(select comment_body from comments where post_id = p.post_id
order by created_date asc) where rownum=1
) the_first_comment
FROM posts p
and it works fine.
它工作正常。
However, in Oracle I'm getting an error ORA-00904 p.post_id: invalid identifier.
但是，在 Oracle 中，我收到错误 ORA-00904 p.post_id: invalid identifier。
It seems to work fine for one subselect, but I cannot get the comment with only one due to the fact that I need to use rownum (no limit / offset in Oracle).
它似乎适用于一个子选择，但由于我需要使用 rownum（在 Oracle 中没有限制/偏移）这一事实，我无法获得只有一个的评论。
What am I doing wrong here?
我在这里做错了什么？
回答by Quassnoi
No, Oracledoesn't correlate the subqueries nested more than one level deep (and neither does MySQL).
不，Oracle不会关联嵌套超过一层的子查询（也不会MySQL）。
This is a well-known problem.
这是一个众所周知的问题。
Use this:
用这个：
SELECT p.post_id, c.*
FROM posts
JOIN (
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_date ASC) AS rn
FROM comments c
) c
ON c.post_id = p.post_id
AND rn = 1
回答by Steve Broberg
If you need SQL that is platform-independent, this will work:
如果您需要独立于平台的 SQL，这将起作用：
SELECT p.post_id
, c.comment_body
FROM posts p
, comments c
WHERE p.post_id = c.post_id
AND c.created_date IN
( SELECT MIN(c2.created_date)
FROM comments c2
WHERE c2.post_id = p.post_id
);
But it assumes that (post_id, created_date) is the primary key of comments. If it isn't, you're going to get more than one line posts that have comments with the same created_date.
但它假定 (post_id, created_date) 是评论的主键。如果不是，您将收到不止一行评论具有相同 created_date 的帖子。
Also, it is likely to be slower than the solution that uses analytics, given by Quassnoi.
此外，它可能比 Quassnoi 提供的使用分析的解决方案慢。
