SQL - 子查询与外部表之间的关系

Question

SQL - 子查询与外部表之间的关系

sqlsubquerycorrelated-subqueryderived-tableinline-view

14

问题

我需要更好地理解在子查询中何时可以引用外部表，以及为什么这种请求是不合适的。我发现了一个正在尝试重构的Oracle SQL查询中的重复，并且当我尝试将引用的表转换为分组子查询时遇到了问题。

以下语句正常工作：

SELECT  t1.*  
FROM    table1 t1, 
INNER JOIN table2 t2 
        on t1.id = t2.id        
        and t2.date = (SELECT max(date) 
                       FROM   table2  
                       WHERE  id = t1.id) --This subquery has access to t1

很不幸，table2 有时会有重复记录，所以我需要先聚合 t2，然后再将其与 t1 连接。但是，当我尝试用子查询来完成此操作时，SQL 引擎突然无法再识别外部表。

SELECT  t1.* 
FROM    table1 t1, 
INNER JOIN (SELECT * 
            FROM  table2 t2
            WHERE t1.id = t2.id              --This loses access to t1
              and t2.date = (SELECT max(date) 
                             FROM   table2 
                             WHERE  id = t1.id)) sub on t1.id = sub.id 
                             --Subquery loses access to t1

我知道这些查询本质上是不同的，但我不明白为什么一个可以工作而另一个不能。

我知道我可以在子查询中复制表引用，并有效地将子查询与外部表分离，但这似乎是一种非常丑陋的方式来完成这个任务（因为需要重复代码和处理）。

有用的参考资料

我发现了这篇关于SQL Server执行顺序的精彩描述：(INNER JOIN ON vs WHERE clause)。我使用的是Oracle，但我认为这应该是通用的。有一个清晰的子句评估顺序（FROM首先），所以我认为任何出现在列表下面的子句都可以访问之前处理的所有信息。我只能假设我的第二个查询会改变那个顺序，以至于我的子查询被过早地评估了？
此外，我还发现了一个类似的问题（Referencing outer query's tables in a subquery ），但虽然输入很好，他们从未真正解释过他为什么不能做他正在做的事情，只是提供了解决问题的替代方案。我尝试了他们的备选解决方案，但它会引起其他问题。换句话说，带有日期引用的子查询对整个操作至关重要，所以我无法摆脱它。

问题

我想理解我在这里做了什么...为什么我的初始子查询可以看到外部表但是在我将整个语句包装在子查询中后就不能看到了？
话虽如此，如果我想做的事情做不到，重构第一个查询以消除重复的最佳方法是什么？我应该两次引用table1（需要所有重复代码）吗？还是有更好的方式来解决这个问题？

提前感谢！

------编辑------

正如一些人推测的那样，上面的这些查询并不是我正在重构的实际查询，而是我遇到的问题的示例。我正在处理的查询要复杂得多，因此我不敢在这里发布它，因为我担心它会使人们离题。

------更新------

所以我向另一位开发人员咨询了一下，他对于我的子查询为什么失去了t1的访问权限提出了一个可能的解释。因为我将这个子查询包含在括号中，他认为这个子查询在表t1被评估之前就被评估了。这肯定可以解释我一直收到的“ORA-00904：“t1”。“id”：无效标识符”的错误。这也表明，与算术运算顺序一样，在语句中添加括号会在某些子句评估中赋予它优先权。如果有专家在这方面表示同意/不同意，我仍然很想听听他们的意见。

- user2858650

1

一个派生表不能被相关联，它必须独立存在。虽然你可以加入它，但当你需要一种类似于相关派生表的排序时，你可以使用 APPLY。 - Martin Smith

Martin...非常感谢你！根据你的评论，我能够进行一些额外的研究，并发现我的查询实际上并没有按照我最初的想法提取数据。此外，你建议使用Apply似乎非常适用（尽管对我来说语法有点不同，因为我正在使用Oracle）。非常感谢你的建议 - 实际上，如果你将其作为答案提交，我会将其标记为正确答案。 - user2858650

3个回答

0

以下查询怎么样：

SELECT t1.* FROM 
(
  SELECT * 
  FROM 
  (
    SELECT t2.id,
    RANK() OVER (PARTITION BY t2.id, t2.date ORDER BY t2.date DESC) AS R  
    FROM table2 t2
  )
  WHERE R = 1
) sub 
INNER JOIN table1 t1 
ON t1.id = sub.id

- Linger

谢谢指出这个问题 - 我已经编辑了上面的例子。实际上我在第二个查询中并没有引入t2.*，那只是这篇文章的一个笔误。 - user2858650

不行，它会给出相同的编译错误。如果 Inner Join 引用一个子查询而没有明确指定 From table1，则它无法识别 t1。具体的错误是 ORA-00904："t1"."id"：无效的标识符。 - user2858650

这是一个不错的尝试，但我仍然得到相同的错误。问题在于它被评估的顺序...它正在尝试在定义t1之前评估子查询，这就是为什么t1无法识别的原因。 - user2858650

嘿！干得好，编译通过了（+1）！我得试着玩一下这个方法，看看能否让它做我需要的事情...不幸的是，目前这个解决方案表现很差（我从未提到过，但table2里有2600万条记录，所以处理起来非常困难）。感谢你的帮助...你绝对让我思考了解决问题的不同方式！谢谢！ - user2858650

-1

在你的第二个例子中，你试图将t1引用向下传递2个级别...你不能这样做，你只能将它向下传递1个级别（这就是为什么第一个例子有效）。如果你提供一个更好的例子来说明你想要做什么，我们可以帮助你重写查询。

- Craig

这也是我一开始的想法，但如果我用一个单独的日期替换(SELECT max(date) FROM table2 WHERE id = t1.id))，它仍然无法编译。问题在于直接在引用外部表的子查询上执行内连接。但为什么呢？ - user2858650

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user2858650 · Accepted Answer

我根据Martin Smith上面的评论（感谢Martin！）弄清楚了这个问题，并希望确保我分享我的发现，以供其他遇到此问题的人参考。

技术考虑

首先，如果我使用正确的术语来描述我的问题，肯定会有所帮助：我上面的第一个语句使用了相关子查询：

这实际上是一种相当低效的数据检索方式，因为它会在外部表中的每一行重新运行子查询。因此，我将寻找消除这类子查询的方法来优化我的代码：

https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_1

另一方面，我的第二个语句使用了 Oracle 中称为内联视图的内容，也被称为 SQL Server 中的派生表：

一个内联视图/派生表在查询开始时创建一个临时的未命名视图，然后将其视为另一个表，直到操作完成。由于编译器在FROM行中看到这些子查询时需要创建一个临时视图，因此这些子查询必须是完全自包含的，没有引用子查询外部的内容。

为什么我所做的事情很愚蠢

在第二个表中，我试图做的实质上是基于对语句之外的另一个表的模糊引用创建一个视图。这就像尝试引用查询中没有明确声明的表中的字段一样。

解决方法

最后，值得注意的是，马丁提出了一种相当聪明但最终效率低下的方法来实现我想做的事情。Apply语句是一个专有的SQL Server函数，但它允许您与派生表之外的对象进行通信。

http://technet.microsoft.com/en-us/library/ms175156(v=SQL.105).aspx

同样的功能在Oracle中可以通过不同的语法实现：

Oracle中SQL Server APPLY的等效物是什么？

最终我将重新评估我的整个查询方法，这意味着我必须从头开始重建它（信不信由你，我最初并没有创建这个怪物！）。感谢所有评论者 - 这确实让我困扰了一段时间，但所有的建议都帮助我找到了正确的方向！