Oracle子查询看不到从外层块2级向上的变量

发布于 2024-08-17 17:38:02 字数 427 浏览 4 评论 0原文

我想在一个查询中获取一篇帖子以及与该帖子相关的第一条评论。这是我在 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：无效标识符。

它似乎对于一个子选择工作得很好，但由于我需要使用 rownum （Oracle 中没有限制/偏移量），所以我无法仅用一个子选择来获得注释。

我在这里做错了什么？

原文

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

and it works fine.

However, in Oracle I'm getting an error 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).

What am I doing wrong here?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你怎么敢 2024-08-24 17:38:02

不，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

No, Oracle doesn't correlate the subqueries nested more than one level deep (and neither does 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

回复收藏 0 原文

十秒萌定你 2024-08-24 17:38:02

如果您需要与平台无关的 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) 是评论的主键。如果不是，您将收到不止一行具有相同创建日期的评论的帖子。

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

If you need SQL that is platform-independent, this will work:

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.

Also, it is likely to be slower than the solution that uses analytics, given by Quassnoi.

回复收藏 0 原文

~没有更多了~