通向T-SQL的阶梯：超越基本级别3：构建相关子查询
来自Gregory Larsen，2014/03/05
系列
本文是楼梯系列的一部分：楼梯到T-SQL：超越基础
从楼梯到T-SQL DML，Gregory Larsen介绍了T-SQL语言的更高级方面，比如子查询。
在这个阶梯的第2级中，我讨论了如何在Transact-SQL语句中使用子查询。这个阶梯级别将通过讨论一种称为关联子查询的子查询类型来扩展子查询主题。我将探讨什么是关联子查询，以及它与普通子查询有何不同。此外，我将向您提供一些Transaction-SQL语句的示例，这些语句超出了基础，并使用相关子查询帮助识别结果集中返回的行，以满足复杂的业务需求。
什么是关联子查询？
在这个阶梯的第2级中，我们了解到普通子查询只是另一个Transact-SQL语句中的SELECT语句，在该语句中，如果独立于外部查询运行，子查询可以返回结果。关联子查询是子查询的一种形式，它不能独立于外部查询运行，因为它包含来自外部查询的一个或多个列。关联子查询与普通子查询一样，有时被称为内部查询。如果关联子查询(内部查询)独立于外部查询运行，它将返回一个错误。由于内部查询的执行取决于外部查询的值，因此称为关联子查询。
相关子查询可以执行多次。对于外部查询中选择的每个候选行，它将运行一次。每个候选行的列值将用于为相关子查询的每次执行提供内部外部查询列的值。包含关联子查询的语句的最终结果将基于相关子查询每次执行的结果。
相关子查询示例的示例数据
为了演示如何使用关联子查询，我需要一些测试数据。我的所有示例都将使用AdventureWorks2008R2数据库，而不是创建自己的测试数据。如果您想继续学习并在您的环境中运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库：http：//msftdbprodsamples.codeplex.com/releases/view/93587
WHERE子句中关联子查询的示例
为了演示WHERE子句中相关子查询的使用，我希望识别那些在单个订单中购买了70多个商品的CustomerID。为了完成这个需求，我可以运行清单1中的代码。
SELECT CustomerID FROM Sales.SalesOrderHeader OH
WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = OH.SalesOrderID) > 70;清单1：WHERE子句中的关联子查询
当我运行清单1中的代码时，我在报告1中得到了输出。
CustomerID ----------- 29712 29722 30048 30107
报告1：运行清单1中的代码时返回的结果
如果您查看清单1中的代码，您将看到我通过使用关联子查询限制了我的WHERE。子查询是我从清单1中提取的相关子查询代码中括号内的代码，并将其放在清单2中。
SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = OH.SalesOrderID清单2：清单1中的子查询代码
如果您运行清单2中的代码，我会发现我得到了一个错误，显示在报告2中。
Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "OH.SalesOrderID " could not be bound.
报告2：运行清单2中的代码时出错
我得到了报告2中显示的错误，因为关联子查询包含对OH.SalesOrderID列的引用，该列是来自外部查询的一列。由于所有相关子查询都引用外部查询中的一个或多个列，因此不能独立于与其关联的外部查询运行它们。不能独立于整个Transact-SQL语句运行子查询，这是关联子查询与普通子查询不同的地方。
这里提供的示例是在WHERE子句中使用关联子查询的一个非常简单的示例。希望通过这样一个简单的例子，可以很容易地理解普通子查询和相关子查询之间的区别。通常，相关子查询可能要复杂得多。此外，请记住，在不使用相关子查询的情况下，可能还有其他方法来满足您的业务需求。
如您所见，编写关联子查询与普通子查询非常相似，但不能独立于外部查询运行关联子查询。
HAVING子句中的关联子查询示例
有时，您可能希望通过外部查询的不同值约束HANING子句。这是您可以在HAVING子句中使用关联子查询的时候。假设您必须编写一个查询，该查询将计算那些在2008年购买了价值超过150000美元的产品税前产品的客户的退税金额。清单3中的代码通过在HAVING子句中使用关联子查询来计算这些有价值客户的回扣金额。
SELECT Outer_H.[CustomerID]
, SUM(Outer_H.[SubTotal]) AS TotalPurchase
, SUM(Outer_H.[SubTotal]) * .10 AS Rebate
FROM [Sales].[SalesOrderHeader] AS Outer_H
WHERE YEAR(Outer_H.[OrderDate]) = '2008'
GROUP BY Outer_H.[CustomerID]
HAVING (SELECT SUM(Inner_H.[SubTotal]) FROM [Sales].[SalesOrderHeader] AS Inner_H
WHERE Inner_H.[CustomerID] = Outer_H.[CustomerID]
AND YEAR(Inner_H.[OrderDate]) = '2008') > 150000
ORDER BY Rebate DESC;清单3：HAVING子句的关联子查询
当我运行清单5中的代码时，我在报告3中得到了结果。
CustomerID TotalPurchase Rebate ----------- --------------------- --------------------------------------- 29923 220496.658 22049.665800 29641 210647.4929 21064.749290 29617 187964.844 18796.484400 29913 186387.5613 18638.756130 29818 179916.2877 17991.628770 29940 175358.3954 17535.839540 29987 172169.4612 17216.946120 29736 157700.6034 15770.060340 29995 156984.5148 15698.451480 29770 151824.9944 15182.499440
报告3：运行清单3的结果
清单3中的关联子查询代码在关联子查询中的外部查询中使用了来自GROUP BY子句的CustomerID。关联子查询将对GROUP BY子句返回的每一行执行一次。这允许HAVING子句通过将每个SalesOrderHeader记录上的小计列的值和外部查询中的CustomerID关联到CustomerID来计算每个CustomerID从外部查询销售到每个CustomerID的产品总量。清单3中的Transact-SQL语句只返回CustomerID购买了价值超过15万美元的产品的一行。
包含关联子查询的UPDATE语句示例
关联子查询不仅可用于使用SELECT语句返回结果集。还可以使用它们更新SQL Server表中的数据。为了演示这一点，我将首先使用清单4中的代码在tempdb表中生成一些测试数据。
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE CarInventory (
ID int identity,
CarName varchar(50),
VIN varchar(50),
StickerPrice decimal (7,2),
InvoicePrice decimal (7,2));
GO
INSERT INTO CarInventory VALUES ('Explorer','EXP2014123456A',46198.45,38201.87),
('Explorer','EXP2014123493A',47129.98, 38201.87),
('Grand Cherokee','JGC20141234345X',41678.45,36201.86),
('Grand Cherokee','JGC20141234556W',44518.31,36201.86),
('Pathfinder','NPF2014987365A',32587.73,28917.10),
('Pathfinder','NPF2014239657B',33577.54,28917.10),
('Pathfinder','NPF2014098587C',35876.12,28917.10),
('Tahoe','TAH201409674A',52001.08,46000.01);清单4：用代码来创建和填充测试表
清单4中的代码创建了一个CarInventory表，然后填充了8行，表示当前库存中的汽车。
销售经理周期性地通过运行清单5中的查询来查看他的InvoicePriceRatio。
SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio
FROM CarInventory;清单5： InvoicePriceRatio查询
当经理运行此查询时，她注意到有许多类似的汽车，其发票金额相同，有不同的InvoicePriceRatio值。为了最大限度地提高她的发票价格，她要求她支持编写一个查询，以更新她所有汽车上的StickerPrice，这样每辆车都有相同的CarName值，就有相同的InvoicePriceRatio。她希望IT人员将StickerPrice设置为与CarName的最大价格相同的值。这样，所有具有相同CarName值的汽车将具有相同的StickerPrice值。为了完成CarInventory表的更新，IT人员运行清单6中的Transact-SQL 语句，其中包含一个相关子查询。
UPDATE CarInventory
SET StickerPrice = (SELECT MAX(StickerPrice)
FROM CarInventory Inner_CI
WHERE Inner_CI.CarName = Outer_CI.CarName)
FROM CarInventory Outer_CI; 清单6：相关子查询来更新CarInventory到最大价格
清单8中的代码使用关联子查询中的外部查询的CarName来标识每个惟一的CarName的最大StickerPrice。然后，在相关子查询中发现的最大StickerPrice值用于更新具有相同名称的每个CarInventory记录的StickerPrice值。
相关子查询的性能考虑
在编写包含相关子查询的Transact-SQL 语句时，应该注意一些性能方面的考虑。当外部查询包含少量行时，性能并不差。但是，当外部查询包含大量的行时，从性能的角度来看，它的伸缩性并不好。这是因为要对外部查询中的每个候选行执行相关的子查询。因此，当外部查询包含越来越多的候选行时，一个相关的子查询必须多次执行，因此Transact-SQL 语句将需要更长的时间运行。如果您发现相关子查询Transact-SQL 语句的性能不符合你的要求，那么你应该寻找替代解决方案，例如使用内部或外部连接操作的查询，或者从外部查询返回少量候选行的查询。
总结
相关子查询是一个内部查询，它包含来自外部查询的一个或多个列。相关子查询对外部查询的每个候选行执行一次。因为相关子查询包含来自外部查询的列，因此它不能独立于外部查询运行。相关子查询有它们的位置，尽管在外部查询中识别出大量候选行时，从性能角度看，它们的伸缩性并不好。
问题和答案
在本节中，您可以通过回答以下问题来回顾您如何理解相关子查询的概念。
问题1:
在编写相关子查询时，需要有___________________。(填入空白)
A、来自内部查询的一个或多个列，用于约束相关子查询的结果。
B、在相关子查询的选择列表中使用的内部查询中的一个或多个列。
C、来自外部查询的一个或多个列，用于约束相关子查询的结果。
D、在相关子查询的选择列表中使用的外部查询的一个或多个列。
问题2:
选择所有关于相关子查询的语句。
A、随着候选行的数量增加，包含相关子查询的Transact-SQL语句的性能得到了提高。
B、相关子查询将对来自外部查询的每个候选行执行一次。
C、相关子查询将引用内部查询中的一个或多个列。
D、当在一个拥有子句中使用相关子查询时，将对由GROUP BY子句返回的每个候选行执行一次内部查询。
问题3:
相关子查询与普通子查询类似，而相关子查询可以独立于整个Transact-SQL语句(True或False)运行。
A、对的
B、错的
答案:
问题1:
正确答案是c .相关子查询需要在相关子查询语句中使用外部查询中的一个或多个列。在执行相关子查询时，这些外部列引用将替换为每个候选行的值。
问题2:
正确的答案是b和d . a是不正确的，因为随着候选行数量的增加，相关子查询的执行次数增加，而Transact-SQL语句性能变得更糟。c是不正确的，因为相关子查询必须包含来自外部查询的一个或多个行，而不是内部查询。
问题3:
正确的答案是b .如果您尝试独立于完整的tTransact-SQL语句运行相关子查询，那么相关的子查询语句将会失败。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
