【译】T-SQL进阶：超越基础（三）：建立一个相关子查询
原文链接：传送门。
在本系列的第二篇文章中，我讨论了如何在一个T-SQL语句中使用子查询。本文我们将扩展下子查询的逻辑，介绍一种被称为相关子查询的子查询。我将会探索什么是相关子查询以及其是如何与正常的子查询区别开来的。另外，我将会提供几个使用了相关子查询T-SQL语句的示例来帮助你识别在结果集中返回的数据行，以满足复杂的业务需求。
什么是相关子查询
在本系列的第二篇文章中，我们知道子查询其实就是在另一个T-SQL语句之中的一个SELECT语句，而这个子查询如果独立于外部查询来运行的话将返回一个结果集。而相关子查询是一种不能独立于外部查询来运行的子查询，这是因为其包含来自于外部查询的一个或者多个列。相关子查询，和一般的子查询一样，也被称之为内部子查询。如果相关子查询独立于外部查询来运行的话，它将会返回一个错误。正是因为内部查询的执行会依赖于来自外部查询的值，其被称之为相关子查询。
相关子查询很可能会被执行很多次。对于在外部查询中选择的每一个候选行，它都会被执行一次。每一个候选行的列值都会被用来为相关子查询的执行提供值。包含一个相关子查询的语句的结果集是每次相关子查询的执行结果。
相关子查询示例数据
为了演示如何使用一个相关子查询，我需要一些测试数据。我所有的示例都使用了 AdventureWorks2008R2 数据库，而没有创建我自己的测试数据。如果你想要在你的环境中运行我的示例，那么你可以从这里下载AdventureWorks2008R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587。
WHERE子句中的相关子查询示例
为了演示在WHERE子句中如何使用相关子查询，假设我想要识别在一个单独的订单中购买超过70件商品的顾客ID，为了完成这个需求，我可以运行列表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中的子查询
如果你运行列表1的代码你将会得到如报告2所示的错误。
Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "OH.SalesOrderID " could not be bound.
报告2：当运行列表2的代码产生的错误
我得到了报告2所示的错误，这是因为我的相关子查询包含了一个到OH.SalesOrderID列的引用，其是一个来自于外部查询的列。既然所有的相关子查询都会包含一个或者多个来自于外部查询的列，因而你不能独立于外部查询来运行它们。你不能独立于外部查询来运行这个相关子查询的事实正是用于区分相关子查询和一般子查询的特征所在。
展示在这儿的这个示例是一个在WHERE子句中使用相关子查询的非常琐碎的示例。希望通过这样一个简单的示例，我们能够轻松的理解一个正常子查询和一个相关子查询之间的不同。通常来说一个相关子查询或许会更加复杂。另外，请记住，或许会有其他的方法来满足你的业务需求，并不一定非要使用相关子查询。
如你所见，编写一个相关子查询与编写一个常规子查询很相似，只是你不能独立于外部查询来运行这个相关子查询。
HAVING子句中的相关子查询的示例
或许会有很多时候你想用外部查询的不同值来约束你的HAVING子句。这个时候你可以在HAVING子句中使用相关子查询。假设您必须编写一个查询，该查询将计算那些在2008年购买了价值超过15万美元的税前产品的客户的折扣金额。清单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子句中的相关子查询
当我运行列表3的代码，我将会得到报告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。对于从GROUB BY子句返回的每一行，相关子查询都会被执行一次。这允许HAVING子句计算销售给来自于外部查询的每个CustomerID的产品的总金额。列表3中的T-SQL语句仅仅返回购买了超过15000美元的产品的CustomerID行。
包含相关子查询的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查询
当主管运行这个查询，他注意到一些具有相同InvoicePrice的车辆却具有不同的InvoicePriceRatio值。为了最大化InvoicePriceRatio，他让他的IT支持人员写一个查询语句，将更新所有车的StickerPrice，从而使得具有相同名字的车具有相同的InvoicePriceRatio。他想要IT将StickerPrice设置为相同CarName的最大值。这种方式下，所有具有相同名称的车便具有相同的StickerPrice值。为了完成这个更新，IT会运行列表6中的T-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表更改为最大的StickerPrice的相关子查询
列表6的代码在相关子查询中使用了来自外部查询的CarName字段，用来为各个唯一的CarName来识别最大的StickerPrice。而这个在相关子查询中发现的最大的StickerPrice值接下来被用来为每一个具有相同CarName的CarInventory 记录更新其StickerPrice值。
相关子查询的性能考虑
当你在写包含相关子查询的T-SQL语句的时候，有几个性能方面的考虑是你应该意识到的。当外部查询包含较少的数据行时，其性能并不算坏。但是当外部循环包含大量的数据行时，从性能的角度来说，其扩展性并不是很好。这是因为相关子查询对于外部查询的每一个候选行都会执行一次。因此当外部查询包含越来越多的数据行时，相关子查询不得不执行很多次，故而T-SQL语句需要花费更长的时间来运行。如果你发现相关子查询语句的性能并不能满足你的需求，那么你应该寻找替代的解决方案，比如使用了INNER JOIN/OUTER JOIN的查询，或者从外部查询返回较小的候选行。
总结
相关子查询是包含了来自于外部查询的一列或者多列的内部子查询。相关子查询对于外部查询的每一个候选行都会执行一次。因为相关子查询包含了来自外部查询的列，因此其不能独立于外部查询而运行。尽管当外部查询具有较多的候选行时其从性能方面考虑其扩展性并不是很好，然而相关子查询仍具有其用武之地。
