外文转译：T-SQL的阶梯：超越基础3级：构建相关的子查询
T-SQL的阶梯：超越基础3级：构建相关的子查询
作者：Gregory Larsen， 2014/03/05
该系列
本文是“Stairway系列：T-SQL的基石：超越基础”的一部分
从他的Stairway到T-SQL DML之后，Gregory Larsen涵盖了T-SQL语言的更多高级方面，例如子查询。
在这个阶梯的第2层，我讨论了如何在Transact-SQL语句中使用子查询。这楼梯水平将通过讨论一类子查询被称为的扩大子查询主题相关SUBQ uery。我将探索什么是相关的子查询，以及它是如何不同于正常的子查询。另外，我将向您提供一些超越基础的Transaction-SQL语句示例，并使用相关的子查询来帮助确定结果集中返回的行，以满足复杂的业务需求。
什么是相关子查询？
在这个阶梯的第2层，我们了解到，正常的子查询只是另一个Transact-SQL语句中的SELECT语句，如果独立于外部查询运行，子查询可以返回结果。相关的子查询是一个子查询的形式，不能独立于外部查询运行，因为它包含来自外部查询的一个或多个列。相关的子查询就像正常的子查询一样，有时被称为内部查询。如果相关子查询（内部查询）独立于外部查询运行，则会返回错误。因为内部查询的执行取决于来自外部查询的值，所以称为相关子查询。
相关的子查询可能会执行很多次。对于在外部查询中选择的每个候选行，它将运行一次。每个候选行的列值将用于为相关子查询的每次执行提供内部外部查询列的值。包含相关子查询的语句的最终结果将基于相关子查询的每个执行结果。
相关子查询示例的示例数据
为了演示如何使用相关的子查询，我将需要一些测试数据。我的所有示例都将使用A dventureWorks2008R2数据库，而不是创建自己的测试数据。如果您想跟随并在您的环境中运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库：http : //msftdbprodsamples.codeplex.com/releases/view/93587
WHERE子句中相关子查询的示例
为了演示如何在WHERE子句中使用相关的子查询，我想要确定那些已经以单一订单购买了70多个项目的CustomerID。为了达到这个要求，我可以运行清单1中的代码。
从Sales.SalesOrderHeader OH中选择CustomerID
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子句中相关子查询的示例
有时您可能想要通过来自外部查询的不同值限制HAVING子句。这是在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]
ANDYEAR（Inner_H。[OrderDate]）='2008'）> 150000
ORDER BY Rebate DESC;
清单3：HAVING子句中的相关子查询
当我运行清单5中的代码时，我在Report 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子句从外部查询中计算销售给每个CustomerID的产品总数，方法是将每个SalesOrderHeader记录上的记录与外部查询中的CustomerID相关联的SubTotal列的值相加。清单3中的Transact-SQL语句只返回一个CustomerID已经购买价值超过$ 150,000的产品的行。
包含相关子查询的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表，然后用八行代表当前正在清单中的汽车进行填充。
销售经理定期地通过运行清单5中的查询来看到他的InvoicePriceRatio。
SELECT CarName，InvoicePrice / StickerPrice * 100.0 AS InvoicePriceRatio
FROM CarInventory;
清单5：InvoicePriceRatio查询
当经理运行此查询时，她注意到有许多具有相同InvoicePrice金额的具有不同InvoicePriceRatio值的类似汽车。为了最大限度地提高她的发票，以标价比她问她的IT支持编写一个查询，将更新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从外部查询的相关子查询，以确定最大StickerPrice为每个唯一的CarName。在相关子查询中找到的最大StickerPrice值将用于更新具有相同CarName的每个CarInventory记录的StickerPrice值。
相关子查询的性能注意事项
在编写包含相关子查询的Transact-SQL语句时，应注意一些性能问题。当外部查询包含少量行时，性能并不差。但是，当外部查询包含大量行时，从性能角度来看，它不能很好地扩展。这是因为相关的子查询需要为外部查询中的每个候选行执行。因此，当外部查询包含越来越多的候选行时，需要多次执行相关子查询，因此Transact-SQL语句将花费更长时间才能运行。如果发现相关子查询的性能Transact-SQL语句不符合您的要求，那么您应该查找其他解决方案，例如使用INNER或OUTER JOIN操作的查询，
概要
相关子查询是包含来自外部查询的一个或多个列的内部查询。相关子查询对于外部查询的每个候选行都执行一次。由于相关子查询包含来自外部查询的列，因此不能独立于外部查询运行。相关的子查询有其自己的位置，但是当在外部查询中标识出大量的候选行时，从性能角度来看不能很好地扩展。
问题和答案
在本节中，您可以通过回答以下问题来查看您是否理解相关子查询的概念。
问题1：
在编写相关的子查询时，你需要有____________________。（填空）
- 内部查询中用于限制相关子查询结果的一列或多列。
- 从相关子查询的选择列表中使用的内部查询中的一个或多个列。
- 来自外部查询的一列或多列用于限制相关子查询的结果。
- 在相关子查询的选择列表中使用的来自外部查询的一个或多个列。
问题2：
选择以下关于相关子查询的所有语句。
- 随着候选行数的增加，包含相关子查询的Transact-SQL语句的性能也会提高。
- 相关子查询将从外部查询中为每个候选行执行一次。
- 相关的子查询将引用来自内部查询的一个或多个列。
- 在HAVING子句中使用相关子查询时，对于由GROUP BY子句返回的每个候选行，内部查询将执行一次。
问题3：
相关的子查询就像正常的子查询，相关的子查询可以独立于整个Transact-SQL语句（True或False）运行。
- 真正
- 假
回答：
问题1：
正确答案是c。相关的子查询需要来自外部查询的一个或多个列在关联的子查询语句中使用。执行相关子查询时，这些外部列引用被替换为每个候选行的值。
问题2：
正确答案是b和d。a是不正确的，因为随着候选行数的增加，相关子查询会执行更多次，并且Transact-SQL语句性能会变差。c不正确，因为相关的子查询必须包含来自外部查询的一个或多个行，而不是内部查询。
问题3：
正确答案是b。如果尝试独立于完整的Transact-SQL语句运行相关的子查询，则相关的子查询语句将失败。
本文是 T-SQL的基础：超越基础的阶梯
注：本文转译于：http://www.sqlservercentral.com/articles/Stairway+Series/105972/，
原文作者：Gregory Larsen
posted on 2017-11-26 12:42 fenglianchen 阅读(112) 评论(0) 编辑 收藏 举报
