第十二周翻译

 

通往t - sql的阶梯:超越基本级别3:构建相关子查询

 

格雷戈里·拉森,2014/03/05

该系列

这篇文章是楼梯系列的一部分:通往t - sql的楼梯:超越基础

从他的阶梯到t - sql DML,Gregory Larsen涵盖了t - sql语言的更高级的方面，如子查询。

在这个楼梯的第二层，我讨论了如何在transact - sql语句中使用子查询。这个楼梯级别将通过讨论一种称为关联子查询的子查询类型来扩展子查询主题。我将探讨什么是相关子查询，以及它与普通子查询的区别。此外，我还将为您提供一些超越基础的事务- sql语句示例，并使用关联子查询来帮助识别结果集中返回的行，以满足复杂的业务需求。

什么是相关子查询?

在这个楼梯的第2级，我们了解到正常的子查询只是在另一个transact - sql语句内的一个SELECT语句，在这个语句中子查询如果独立于外部查询而返回结果。关联子查询是子查询的一种形式，它不能独立于外部查询运行，因为它包含来自外部查询的一个或多个列。相关子查询，就像普通的子查询，有时被称为内部查询。如果相关子查询(内部查询)独立于外部查询运行，则它将返回一个错误。因为内部查询的执行依赖于来自外部查询的值，因此它被称为相关子查询。

相关子查询可以执行很多次。它将为在外部查询中选择的每个候选行运行一次。每个候选行的列值将用于为关联子查询的每次执行的内部的外部查询列提供值。包含相关子查询的语句的最终结果将基于相关子查询的每次执行的结果。

相关子查询示例的示例数据

为了演示如何使用相关子查询，我需要一些测试数据。我的所有示例都将使用AdventureWorks2008R2数据库，而不是创建自己的测试数据。如果你想跟随并运行在您的环境中我的例子你可以从这里下载AdventureWorks2008R2数据库:

http://msftdbprodsamples.codeplex.com/releases/view/93587

 

在WHERE子句中关联子查询的示例

为了演示在WHERE子句中使用关联子查询，我想要确定这些CustomerID在单个订单中购买了超过70个项目。为了达到这个要求，我可以运行清单1中的代码。

 

清单1:在WHERE子句中关联子查询

当我运行清单1中的代码时，我得到了报告1中的输出。

 

报告1:运行清单1中的代码时返回的结果

如果您回顾清单1中的代码，您将看到我使用相关子查询限制了我的位置。子查询是圆括号中的代码，我从清单1中提取了相关的子查询代码，并将其放入清单2中。

 

如果你查看清单1中的代码，你将看到我使用一个相关的子查询来限制我的位置。子查询是括号内的代码，我从清单1中提取了相关的子查询代码，并将其放在清单2中。

 

清单2:清单1中的子查询代码

如果我运行清单2中的代码，我将发现在报告2中显示了一个错误。

 

报告2:在清单2中运行代码时出错

我得到了报告2中显示的错误，因为我的相关子查询包含了对列的引用。来自外部查询的一个列。由于所有相关的子查询引用了外部查询中的一个或多个列，所以不能独立地运行它们与它关联的外部查询。您不能独立于整个transact-sql语句运行子查询，这是与普通子查询相关的子查询的区别所在。

这里给出的示例是在WHERE子句中使用相关子查询的一个非常简单的示例。希望通过这样一个简单的示例，可以很容易地理解普通子查询和相关子查询之间的区别。通常，一个相关的子查询可能要复杂得多。此外，请记住，在不使用相关子查询的情况下，可能会有其他方法满足你的业务需求。

正如您所看到的，编写一个相关的子查询与普通的子查询非常相似，但是你不能独立地运行与外部查询无关的子查询。

在有子句中关联子查询的例子

有时候，你可能希望使用来自外部查询的不同值来约束一个子句。这是在你的子句中可以使用相关子查询的时候。假设你必须编写一个查询，该查询将为那些在2008年之前购买了超过15万美元的产品的客户计算折扣金额。清单3中的代码通过使用子句中的关联子查询来计算那些有价值的客户的折扣金额。

 

 

清单3:在有子句中的相关子查询

当我运行清单5中的代码时，我得到了报告3中的结果。

 

 

报告3:运行清单3的结果

清单3中的相关子查询代码使用关联子查询中的外部查询中的GROUP BY子句中的CustomerID。关联的子查询将针对从GROUP BY子句返回的每一行执行一次。这允许使用条款计算从外部查询中销售给每个CustomerID的产品的总数量，通过对每个销售订单记录的子总列的值进行汇总，其中记录与来自外部查询的CustomerID相关联。清单3中的transact-sql语句只返回一行，其中的CustomerID已经购买了价值超过15万美元的产品。

ransact-SQL语句在清单3中只返回一行，CustomerID在购买了价值超过150000美元的产品。

 

UPDATE语句包含一个相关子查询的例子

相关子查询不仅可以用于返回一个结果集，使用SELECT语句。还可以使用它们更新SQLServer表中的数据。为了证明这一点，我会先在tempdb表生成一些测试数据，通过使用清单4中的代码。

清单4：创建和填充测试表的代码

 

清单4中的代码创建一个CarInventory表然后填充八行代表车当前库存。

定期的销售经理喜欢通过运行清单5中的查询看到他的InvoicePriceRatio。

 

清单5：InvoicePriceRatio 查询

当经理运行此查询她注意到有一些相同的发票价量有不同的 InvoicePriceRatio值类似的汽车。为了最大化自己的发票价格比她问她写一个查询，将更新所有她的车，每一辆车的StickerPrice同Carname值具有相同的 InvoicePriceRatioIT支持。她希望这家伙把StickerPrice作为Carname最高标价相同的值。这样，所有的汽车都有相同的carname值都有相同的Stickerprice价值。为了完成这个更新的Carinventory表，这家伙的交易清单6中的SQL语句，其中包含一个相关子查询。

 

 

清单6：相关子查询更新Carinventory Maximum Sticker Price

有一些性能方面的考虑，你应该知道在编写Transact-SQL语句包含相关子查询。当外部查询包含少量行时，性能并不差。但是，当外部查询包含大量行时，它不会从性能角度很好地扩展。这是因为相关子查询需要外部查询中的每个候选行执行。因此，当外部查询包含越来越多的候选行相关子查询被执行多次，因此Transact-SQL语句将需要更长的时间来运行。如果你发现你的相关子查询的SQL语句的性能不能满足你的要求，那么你应该寻找替代解决方案，如使用内部或外部连接操作的查询，或是返回一个小数量的候选行从外部查询。

 

总结

相关子查询是一种内在的查询，包括从外部查询的一个或多个列。相关子查询被执行一次外部查询的每个候选行。因为一个相关子查询包含从外部查询不能列可独立运行的外部查询。相关子查询的地方，虽然没有规模从性能的角度来看，有大量的候选人排在外查询鉴定。

问题和答案

在这一部分中你可以回顾一下你如何理解相关子查询的概念，通过回答下列问题。

问题1

当写一个相关子查询时，你需要（ ）

1. 来自内部查询一个或多个列，用来限制相关子查询的结果。
2. 来自内部查询的一个或多个列，用于选择相关子查询的清单。
3. 来自外部查询一个或多个列，用来限制相关子查询的结果。
4. 来自外部查询的一个或多个列，用于选择相关子查询的清单。

问题2

从以下句子中选择所有陈述是真实的相关子查询（）

1. 当候选行的数量增加时，性能的Transact-SQL语句包含一个相关子查询提高了。
2. 相关子查询会执行一次候选行从外部查询
3. 相关子查询将涉及到来自内部查询的一个或多个列
4. 使用相关子查询在HAVING子句内的查询将被执行一次的每个候选行由GROUP BY子句。

问题3

相关子查询就像一个查询，和相关子查询能够独立运行的Transact-SQL语句（真或假）。

1. 真
2. 假

回答

问题1

正确答案是C.一个相关子查询需要从外部查询一个或多个列，是相关子查询语句中使用。这些外部列引用替换成每一个候选行的值执行相关子查询的时候。

问题2

正确答案是B和D是错误的因为作为候选的行数增加相关子查询被执行多次，和Transact-SQL语句性能越差。C是错误的因为相关子查询必须包含从外部查询的一行或多行，不是内部查询。

问题3

正确答案是B。如果你尝试运行相关子查询独立的完整的Transact-SQL语句，相关子查询语句将失败。

本文是对T-SQL楼梯部分：除了基本的楼梯