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

在这个阶梯的第2级中，我讨论了如何在Transact-SQL语句中使用子查询。这个阶梯级别将通过讨论一种名为“子查询”的子查询类型来扩展子查询主题。相关子q尤利。我将探讨什么是关联子查询，以及它与普通子查询有何不同。此外，我将向您提供一些事务SQL语句的示例，这些语句超出了基础，并使用相关子查询帮助识别结果集中返回的行，以满足复杂的业务需求。

什么是关联子查询？

在这个阶梯的第2级中，我们了解到普通子查询只是另一个Transact-SQL语句中的SELECT语句，在该语句中，如果独立于外部查询运行，子查询可以返回结果。关联子查询是子查询的一种形式，它不能独立于外部查询运行，因为它包含来自外部查询的一个或多个列。关联子查询与普通子查询一样，有时被称为内部查询。如果关联子查询(内部查询)独立于外部查询运行，它将返回一个错误。由于内部查询的执行取决于外部查询的值，因此称为相关子查询.

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

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

为了演示如何使用关联子查询，我需要一些测试数据。与其创建我自己的测试数据，我的所有示例都将使用AdventureWorks2008R2数据库。如果您想在您的环境中继续并运行我的示例，则可以下载AdventureWorks2008R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587

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

为了演示在WHERE子句中使用关联子查询，我想要识别顾客ID在一次订购中购买了70多件物品。为了完成这个需求，我可以运行清单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

Report 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子句中使用关联子查询的一个非常简单的示例。希望通过这样一个简单的例子，可以很容易地理解普通子查询和相关子查询之间的区别。通常，相关子查询可能要复杂得多。此外，请记住，在不使用相关子查询的情况下，可能还有其他方法来满足您的业务需求。

如您所见，编写关联子查询与普通子查询非常相似，但不能独立于外部查询运行关联子查询。

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

有时，您可能希望通过外部查询的不同值约束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：有子句的关联子查询

当我运行清单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

Report 3：运行清单3的结果

清单3中的相关子查询代码使用顾客ID在关联子查询中的外部查询中的GROUPBY子句。关联子查询将对GROUPBY子句返回的每一行执行一次。这使得HEACH子句可以计算出每个产品的总销售量。顾客ID的值之和，从而从外部查询中获取小计每一栏SalesOrderHeader记录与顾客ID从外部查询。清单3中的Transact-SQL语句只返回一个行，其中顾客ID已购买了价值超过15万美元的产品。

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

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

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中的代码创建了一个卡英文托里表中，然后用8行填充它，表示当前库存中的汽车。

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

SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio 
FROM CarInventory;

清单5：InvoicePriceRatio查询

当经理运行这个查询时，她注意到有许多类似的汽车都有相同的。InvoicePrice数额不同InvoicePriceRatio价值。为了最大限度地提高发票到贴纸价格的比率，她要求IT支持编写一个查询，以更新粘性价格在她所有的车里所以每辆车都有相同的CarName 值具有相同的InvoicePriceRatio。她想让IT人把粘性价格的最高标价。CarName。这样所有的车都一样CarName粘性价格价值。若要完成此更新，请执行卡英文托里表中，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：用于更新的关联子查询卡英文托里到最高标价

清单8中的代码使用CarName从关联子查询中的外部查询中确定最大值。粘性价格对于每一个唯一的CarName。这个最大值粘性价格然后使用关联子查询中找到的值更新粘性价格每个卡英文托里具有相同记录的记录CarName.

相关子查询的性能考虑

在编写包含相关子查询的Transact-SQL语句时，您应该注意到一些性能问题。当外部查询包含少量行时，性能并不差。但是，当外部查询包含大量行时，从性能的角度看，它的缩放效果并不好。这是因为需要对外部查询中的每个候选行执行关联子查询。因此，当外部查询包含越来越多的候选行时，必须多次执行相关的子查询，因此Transact-SQL语句的运行时间将更长。如果您发现相关子查询Transact-SQL语句的性能不符合您的要求，那么您应该寻找其他解决方案，例如使用内部或外部联接操作的查询，或者从外部查询返回较少数量候选行的查询。

摘要

关联子查询是包含外部查询中的一个或多个列的内部查询。关联子查询对外部查询的每个候选行执行一次。因为关联子查询包含来自外部查询的列，因此不能独立于外部查询运行。关联子查询有它们的位置，尽管在外部查询中标识了大量候选行时，从性能角度看不太合适。

问答

在本节中，您可以通过回答以下问题来了解相关子查询的概念。

问题1：

编写相关子查询时，需要有___

1. 内部查询中用于约束相关子查询结果的一个或多个列。
2. 在相关子查询的选择列表中使用的内部查询中的一个或多个列。
3. 外部查询中用于约束相关子查询结果的一个或多个列。
4. 外部查询中用于相关子查询的选择列表中的一个或多个列。

问题2：

选择下列所有有关相关子查询的语句。

1. 随着候选行数的增加，包含相关子查询的Transact-SQL语句的性能也会提高。
2. 相关子查询将对外部查询中的每个候选行执行一次。
3. 关联子查询将引用内部查询中的一个或多个列。
4. 当在HAVING子句中使用关联子查询时，内部查询将针对GROUPBY子句返回的每个候选行执行一次。

问题3：

关联子查询就像普通的子查询一样，相关子查询可以独立于整个Transact-SQL语句(真或假)运行。

1. 千真万确
2. 假的

答案：

问题1：

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

问题2：

正确的答案是b和D.a是不正确的，因为随着候选行数的增加，相关子查询将被执行更多次，而Transact-SQL语句的性能会变得更糟。c是不正确的，因为相关子查询必须包含来自外部查询的一个或多个行，而不是内部查询。

问题3：

正确的答案是b。如果您试图独立于完整Transact-SQL语句运行关联子查询，则关联子查询语句将失败。