Oracle子查询无法看到来自外部块的变量，距离外部块有2个层级。

14 浏览
0 Comments

Oracle子查询无法看到来自外部块的变量，距离外部块有2个层级。

我想在一个查询中获取帖子和与帖子关联的第一条评论。以下是我在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

在PostgreSQL中，这种做法是有效的。

然而，在Oracle中，我遇到了错误ORA-00904 p.post_id：无效的标识符。

这种方法对于一个子查询似乎是有效的，但是我无法仅使用一个子查询获取评论，因为我需要使用rownum（Oracle中没有limit/offset）。

我在这里做错了什么？

0

2 答案

0 Comments

Oracle子查询不能看到来自外部块的变量的原因是因为子查询在其自己的作用域中运行，并且不能访问外部块中定义的变量。在上面的代码中，子查询使用了变量p.post_id，但是无法在子查询中访问到。

解决这个问题的方法是使用连接操作符（JOIN）来代替子查询。通过将子查询的逻辑转换为连接操作，可以避免子查询无法访问外部变量的限制。下面是使用连接操作符解决这个问题的示例代码：

SELECT p.post_id
     , c.comment_body
  FROM posts p
  JOIN comments c ON p.post_id = c.post_id
  JOIN
    ( SELECT c2.post_id, MIN(c2.created_date) AS min_date
       FROM comments c2
      GROUP BY c2.post_id
    ) subq ON c.post_id = subq.post_id AND c.created_date = subq.min_date;

通过将子查询的逻辑移动到连接操作中的子查询（subq），可以在连接操作的上下文中使用外部变量p.post_id。通过这种方式，可以避免子查询无法访问外部变量的问题。

这种方法不仅解决了子查询无法访问外部变量的问题，还可以更高效地执行查询操作。连接操作通常比子查询更有效率，特别是在处理大量数据时。因此，使用连接操作代替子查询可以提高查询性能。

总结起来，Oracle子查询无法访问外部变量的原因是子查询在其自己的作用域中运行。为了解决这个问题，可以使用连接操作符代替子查询，并将子查询的逻辑移动到连接操作的子查询中。这样可以避免子查询无法访问外部变量的限制，并且提升查询性能。

0
0 Comments

Oracle subquery does not see the variable from the outer block 2 levels up这个问题的出现原因是Oracle在嵌套超过一层的子查询中不进行关联。这是一个众所周知的问题。为了解决这个问题，可以通过将被过滤的变量拉升到子查询的上一层来实现。

解决方法如下：

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不关联超过一层的子查询的问题。

这个解决方法是由Quassnoi提供的，虽然有点复杂，但是对于解决这个问题非常有效。需要注意的是，在使用这个解决方法时，可能需要对Oracle进行一些特殊配置，以确保解决方法的有效性。

需要注意的是，Oracle并不是唯一存在这个问题的数据库，MySQL也存在这个问题。因此，这个解决方法同样适用于MySQL。

虽然这个问题在Oracle和MySQL中都是众所周知的，但是这个问题并没有得到官方的正式文档记录。然而，在ANSI标准中有对这个问题的描述。

总结起来，Oracle subquery does not see the variable from the outer block 2 levels up这个问题的出现原因是Oracle在嵌套超过一层的子查询中不进行关联。为了解决这个问题，可以通过将被过滤的变量拉升到子查询的上一层来实现。这个解决方法同样适用于MySQL。虽然这个问题在Oracle和MySQL中都是众所周知的，但是这个问题并没有得到官方的正式文档记录。然而，在ANSI标准中有对这个问题的描述。

0