Oracle子查询无法看到来自外部2级块的变量

Question

我想在一个查询中获取一篇文章以及与该文章相关的第一条评论。以下是我在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: invalid identifier。

对于一个子查询，它似乎可以正常工作，但是由于我需要使用rownum（Oracle中没有limit/offset），我无法仅获取一个评论。

这里出了什么问题？

- user248789

---

可能你在 PostgreSQL 上使用了 LIMIT 1，它不支持 rownum。 - Quassnoi

是的，当然在Postgres中使用limit。 - user248789

Answer 1

不，Oracle 不会对嵌套超过一级的子查询进行关联（MySQL 也是如此）。

这是一个众所周知的问题。

使用以下代码：

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

- Quassnoi

---

谢谢Quassnoi，这当然是一个好答案（虽然有点复杂，但可能是因为我被PostgreSQL宠坏了）。很抱歉没有早些回复，但我的雇主不喜欢我使用StackOverflow，会阻止该网站的访问，而且使用代理时我只能提问，无法评论（可能是SO ajax代码的问题）。 - user248789

如果你有创意，可以通过将过滤变量提升到子查询的上一级来解决这个问题。 - chotchki

+1 为指出Oracle的问题并展示解决方法。顺便说一句：我确实喜欢Oracle，但有时候Postgres使用起来真的太容易了。 - Christian

不，Oracle不会关联嵌套超过一层深的子查询（MySQL也是如此）。这是一个众所周知的问题。这个问题有文档记录吗？ - Vladimir Ulchenko

@vavan：你使用的是哪个版本的Oracle？ - Quassnoi

Answer 2

如果您需要跨平台的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
        );

但是它假设（post_id，created_date）是评论的主键。如果不是，您将获得具有相同created_date的评论的多行帖子。

此外，与Quassnoi提供的使用分析的解决方案相比，它可能会更慢。

- Steve Broberg

---

那可能有点危险。创建日期不能保证唯一性。但只使用MIN(comment_id)应该没问题，因为它是从序列中获取的。不过我使用了Quassnoi的解决方案。 - user248789