翻译（12w）

楼梯T-SQL：超越基础水平3：建立一个相关子查询

Gregory Larsen，2014 / 03 / 05

该系列

本文是系列的一部分：楼梯楼梯T-SQL：超越基础

下面就从他的楼梯T-SQL DML、Gregory Larsen覆盖的T-SQL语言如子查询更先进的方面。

在这楼梯2级我讨论如何在Transact-SQL语句中使用子查询。这楼梯水平将扩大对查询主题的讨论类型的查询称为相关子查询。我将探讨什么是相关子查询以及它是如何不同于普通的子查询。此外，我将提供你一些事务的SQL语句，超越基本的例子和使用相关子查询来帮助识别返回的行的结果集，以满足复杂的业务需求。

相关子查询实例样本数据

为了演示如何使用相关子查询我需要一些测试数据。而不是创建自己的测试数据，我所有的例子将使用adventureworks2008r2数据库。如果你想跟着我，跑在您的环境中的例子你可以从这里下载adventureworks2008r2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587

在一个相关子查询的WHERE子句的例子

SELECT CustomerID FROM Sales.SalesOrderHeader OH
WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail
         WHERE SalesOrderID = OH.SalesOrderID) > 70;

展示一个相关子查询的WHERE子句中使用的假设，我想找出那些CustomerID已经购买了70多个款式。为了完成这个需求，我可以运行清单1中的代码

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

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

CustomerID
-----------
29712
29722
30048
30107

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

如果你回顾清单1中的代码，你会看到我约束我，通过使用相关子查询。子查询的代码是在圆括号中的我的相关子查询代码清单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子句中使用相关子查询。假设你必须写一个查询，计算那些在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
----------- --------------------- ---------------------------------------
      220496.658            22049.665800
      210647.4929           21064.749290
      187964.844            18796.484400
      186387.5613           18638.756130
      179916.2877           17991.628770
      175358.3954           17535.839540
      172169.4612           17216.946120
      157700.6034           15770.060340
      156984.5148           15698.451480
      151824.9944           15182.499440
 

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

相关子查询清单3中的代码使用CustomerID从集团外部查询中的条款在相关子查询。相关子查询的执行将为每一行返回GROUP BY子句。这使得HAVING子句计算总金额的产品从外部查询卖给每个CustomerID的值求和，次全列每salesorderheader记录在记录与从外部查询相关信息。Transact-SQL语句在清单3中只返回一行，CustomerID在购买了价值超过150000美元的产品。

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中的代码创建一个carinventory表然后填充八行代表车当前库存。

定期的销售经理喜欢通过运行清单5中的查询看到他的发票价格比

SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio

FROM CarInventory;

清单5:发票价格比率查询

当经理运行这个查询时，她注意到有很多类似的汽车，发票价格相同，有不同的发票价格比率值。为了使她的发票最大化，她要求她的IT支持写一个查询，将更新她所有汽车上的标价，这样每辆车的名称相同的汽车都有相同的发票价格比率。她希望IT人员将标价与汽车名称的最高标价相同。这样所有汽车名称相同的汽车都有相同的标价。为了完成汽车库存表的更新，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:

在编写相关子查询时，需要有___________________。(填入空白)

来自内部查询的一个或多个列，用于约束相关子查询的结果。

在相关子查询的选择列表中使用的内部查询中的一个或多个列。

来自外部查询的一个或多个列，用于约束相关子查询的结果。

在相关子查询的选择列表中使用的外部查询的一个或多个列。

问题2:

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

随着候选行的数量增加，包含相关子查询的transact - sql语句的性能得到了提高。

相关子查询将对来自外部查询的每个候选行执行一次。

相关子查询将引用内部查询中的一个或多个列。

当在一个拥有子句中使用相关子查询时，将对由GROUP by子句返回的每个候选行执行一次内部查询。

问题3:

相关子查询与普通子查询类似，而相关子查询可以独立于整个transact - sql语句(True或False)运行。

真实的

假的

答案:

问题1:

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

问题2:

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

问题3:

正确的答案是b .如果您尝试独立于完整的transact - sql语句运行相关子查询，那么相关的子查询语句将会失败。

这篇文章是通往t - sql的楼梯的一部分:除了基本的楼梯

注册到我们的RSS频道，一旦我们在楼梯上发布一个新的级别，就会得到通知!

本文链接地址：

http://www.sqlservercentral.com/articles/Stairway+Series/105972/

posted on 2017-11-29 19:53 54Mosen 阅读(92) 评论(0) 收藏举报

刷新页面返回顶部

54Mosen

翻译（12w）

导航

公告