Stairway to T-SQL: Beyond The Basics Level 3: Building a Correlated Subquery

最新推荐文章于 2018-05-20 21:12:12 发布

微风塘塘

最新推荐文章于 2018-05-20 21:12:12 发布

阅读量190

点赞数

通往T-SQL的Stairway:超越基础3级:构建相关的子查询。

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

该系列

这篇文章是Stairway系列的一部分:T-SQL的Stairway:超越基础。

从他的Stairway到T-SQL DML，格雷戈里·拉森涵盖了T-SQL语言的更高级的方面，比如子查询。

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

什么是相关子查询?

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

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

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

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

W为了演示相关的子查询在WHERE子句假设中的使用，我想识别那些以一个顺序购买了超过70个项的CustomerID。为了实现这个要求，我可以运行清单1中的代码。HERE子句中的关联子查询示例.

清单1：WHERE子句中的关联子查询

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

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

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

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

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

报告2：运行清单2中的代码时出错

我得到了报告2中显示的错误，因为我的相关子查询包含对列OH.SaleOrthID的引用，SaleOrthID是来自外部查询的列。由于所有相关子查询都引用外部查询中的一个或多个列，因此不能独立于与其关联的外部查询运行它们。不能独立于整个Transact-SQL语句运行子查询的事实是区分相关子查询与普通子查询的区别。

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

具有子句的关联子查询示例

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

清单3：有条件子句中的关联子查询

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

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

清单3中的相关子查询代码使用来自相关子查询中的外部查询中的GROUPBY子句的CustomerID。相关的子查询将从GROUPBY子句返回的每行各执行一次。这允许HAVE子句通过计算每个SalesOrderHeader记录中的小计列的值来计算从外部查询卖给每个客户ID的总产品量，其中记录与外部查询中的CustomerID相关联。清单3中的Transact-SQL语句只返回一个行，其中CustomerID已经购买了价值超过150000美元的产品。

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

相关的子查询不仅可以用于使用SELECT语句返回结果集。您还可以使用它们来更新SQLServer表中的数据。为了演示这一点，我将首先使用清单4中的代码在TEMPDB表中生成一些测试数据。

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

清单4中的代码创建了一个CarInventory表，然后用八行表示当前库存中的汽车。

销售经理周期性地通过运行清单5中的查询来查看他的InvoicePriceRatio。

5 invoicepriceratio查询清单：

当管理者运行这个查询时，她注意到有许多类似的车具有相同的InvoicePrice总额，但具有不同的价值。为了最大化她的Invoice到Sticker价格比，她要求IT人员支持写一个查询，将更新StickerPrice的所有车，所以每辆车具有相同的CarName值和具有相同的InvoicePriceRatio。她希望IT人员将StickerPrice设置为与CarName的最大Sticker价格相同的值。这样，所有具有相同CarName值的汽车将具有相同的StickerPrice值。为了完成CarInventory表的更新，IT人员运行清单6中的Transact-SQL语句，以及其中包含相关的子查询。

清单6：相关子查询更新Maximum Sticker Price的库存清单

清单8中的代码使用关联子查询中的外部查询的CarName来标识每个唯一的CarName的最大StickerPrice。然后，在相关子查询中找到的最大StickerPrice值用于更新具有相同CarName的每个库存记录的StickerPrice值。