Oracle子查询没有从外部块2级别上看到该变量 [英] Oracle subquery does not see the variable from the outer block 2 levels up
问题描述
我想在一个查询中获得帖子以及与该帖子相关联的第一条评论.这是我在PostgreSQL中的操作方式:
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:
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
它工作正常.
但是,在Oracle中出现错误ORA-00904 p.post_id:无效的标识符.
However, in Oracle I'm getting an error ORA-00904 p.post_id: invalid identifier.
对于一个子选择似乎很好用,但是由于我需要使用rownum(在Oracle中没有限制/偏移量)这一事实,我无法仅获得一个注释.
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).
我在做什么错了?
推荐答案
不,Oracle
不与嵌套多于一个深度的子查询相关(MySQL
也不相关).
No, Oracle
doesn't correlate the subqueries nested more than one level deep (and neither does MySQL
).
这是一个众所周知的问题.
This is a well-known problem.
使用此:
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
这篇关于Oracle子查询没有从外部块2级别上看到该变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!