通往T-SQL的阶梯:超越基础3级:构建相关的子查询

最新推荐文章于 2022-03-06 15:18:20 发布

qq152521766

最新推荐文章于 2022-03-06 15:18:20 发布

阅读量169

点赞数

通往T-SQL的阶梯:超越基础3级:构建相关的子查询。

格雷戈里·拉森,2014/03/05

系列

这篇文章是阶梯系列的一部分:T-SQL的阶梯:超越基础。

从他的阶梯系列到T-SQL DML，格雷戈里·拉森涵盖了T-SQL语言的更高级的方面，比如子查询。

在这个楼梯的第2级，我讨论了如何在Transact-SQL语句中使用子查询。

通过讨论一种称为关联子查询的子查询类型，这个楼梯级别将扩展到子查询主题。

我将探讨什么是相关子查询，以及它与普通子查询的区别。

此外，我还将提供一些超越基础的事务- 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中的代码, 您将看到我使用相关子查询限制了我的位置。子查询是括号内的代码我从清单1中提取了相关的子查询代码, 并将其放在清单2中。

SELECT COUNT(*) FROM Sales.SalesOrderDetail

WHERE SalesOrderID = OH.SalesOrderID

清单 2: 清单1中的子查询代码

如果我运行清单2中的代码, 我会发现在报告2中显示了一个错误。

Msg 4104, Level 16, State 1, Line 3The multi-part identifier "OH.SalesOrderID" could not be bound.

报告 2: 运行清单2中的代码时出错

我收到报告2中显示的错误, 因为我的相关子查询包含对列的引用哦。SalesOrderID 是外部查询中的列。由于所有相关子查询都引用外部查询中的一个或多个列, 因此无法独立于与其关联的外部查询来运行它们。不能独立于整个 transact-sql 语句运行子查询的事实是将相关子查询与正常子查询区别开来的原因。

此处提供的示例是在 where 子句中使用相关子查询的非常简单的示例。希望通过这样一个简单的示例, 可以很容易地理解正常子查询和相关子查询之间的区别。通常, 相关的子查询可能相当复杂。此外, 请记住, 在不使用相关子查询的情况下, 可能还有其他方法可以满足业务需求。

正如您所看到的, 编写相关子查询与普通子查询非常相似, 但您不能独立地运行外部查询的相关子查询。

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

有时您可能希望通过外部查询不同的值来约束HAVE子句。这是您可以在HAVE子句中使用相关子查询的时候。假设你必须写一个查询，它将计算那些在2008年度购买税前产品超过150000美元的客户的折扣金额。清单3中的代码通过使用HAVE子句中的关联子查询来计算那些客户的折扣金额。

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

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

清单3中的相关子查询代码使用来自相关子查询中的外部查询中的GROUPBY子句的客户ID。相关的子查询将从GROUPBY子句返回的每行执行一次。这允许HAVE子句通过计算每个销售订单报头记录中的小计列的值来计算从外部查询卖给每个客户ID的总产品量，其中记录与外部查询中的客户ID相关联。清单3中的Transact-SQL语句只返回一个行，其中客户机ID已经购买了价值超过150000美元的产品。

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

相关的子查询不仅可以用于使用SELECT语句返回结果集。您还可以使用它们来更新SQL Server表中的数据。为了演示这一点，我将首先使用清单4中的代码在TEMPDB表中生成一些测试数据。

USE tempdb;

SET NOCOUNT ON;

CREATE TABLE CarInventory (

ID int identity,

CarName varchar(50),

VIN varchar(50),

StickerPrice decimal (7,2),

InvoicePrice decimal (7,2));

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中的代码创建了一个存货清单表，然后用八行表示当前库存中的汽车。销售经理周期性地通过运行清单5中的查询来查看他的发票价格。

SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio

FROM CarInventory;

清单5:InvoicePriceRatio查询
当管理人员运行这个查询时，她注意到有许多类似的汽车，其发票金额与发票金额不同。为了最大限度地提高她的发票价格，她要求她的IT支持写一个查询，以更新她所有汽车的粘着价格，这样每辆车都有相同的车名值，这是相同的发票。她想让IT男把StickerPrice设置为与Car Name的最大标价相同的价格。这样，所有具有相同CarName值的汽车将具有相同的StickerPrice值。为了完成存货清单表的更新，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语句时，需要注意一些性能方面的考虑。

当外部查询包含少量行时，性能并不差。

但是，当外部查询包含大量的行时，从性能的角度来看，它的伸缩性并不好。

这是因为在外部查询中每个候选行都需要执行相关的子查询。