翻译：通向T-SQL的阶梯:超越基础水平3:建立相关子查询
原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/105972/ 原文作者：Gregory Larsen
该系列
从他的t - sql DML楼梯后,格雷戈里·拉森涵盖了更高级的子查询等方面的t - sql语言。
在2级楼梯我讨论了如何使用数据库引擎执行子查询的sql语句。 这楼梯水平将扩大在子查询主题讨论类型的子查询称为相关subquery。 我将探索什么是相关子查询,以及它是如何不同于正常的子查询。 另外我将提供你一些Transaction-SQL语句的例子超越基本知识和使用相关子查询来帮助识别行返回的结果集,以满足复杂的业务需求。
相关子查询是什么?
在2级楼梯我们了解到一个正常的子查询只是一个SELECT语句在数据库引擎执行另一个sql语句,子查询可以返回结果,如果独立运行的外部查询。 相关子查询是一种外的子查询,不能独立运行查询,因为它包含一个或多个列从外部查询。 相关子查询,就像一个正常的子查询,有时被称为内部查询。 如果相关子查询(内部查询)独立运行的外部查询将返回一个错误。 因为内部查询的执行取决于价值观从外部查询,它被称为相关子查询。
相关子查询可能多次执行。 它将为每个选定的候选人行运行一次外层查询。 每个候选人一行的列值将用于供应值外查询中的列内的每个执行相关子查询。 最后声明,包含一个相关子查询的结果将基于每个执行相关子查询的结果。
样本数据的相关子查询的例子
为了演示如何使用相关子查询我需要一些测试数据。 而不是创建我自己的测试数据,我将使用一个示例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中您将看到我约束,通过使用相关子查询。 括号内的子查询的代码我已经提取了相关子查询代码与清单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所示的错误,因为相关子查询列包含一个引用哦。 SalesOrderID列从外部查询。 因为所有相关子查询引用一个或多个列外查询不能独立地运行它们的外层查询与之相关的。 你不能独立运行子查询整个transact - sql语句的区分在一个正常的子查询相关子查询。
这里给出的例子是一个非常简单的例子在WHERE子句中使用相关子查询。 希望通过这样一个简单的例子是很容易理解的区别正常的子查询和相关子查询。 典型相关子查询可能更加复杂。 此外,记住可能有其他方法来满足您的业务需求不相关子查询。
正如你所看到的写相关子查询非常类似于一个正常的子查询,但你不能独立于外部查询运行相关子查询。
相关子查询的例子HAVING子句
有些时候你可能需要限制条款由外层查询不同的值。 这是当你可以使用子查询相关条款。 假设你要编写一个查询,计算退税金额的客户购买了价值超过150000美元税收在2008年前的产品。 清单3中的代码计算退税金额为那些有价值的客户通过使用相关子查询的条款。
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中使用的相关子查询代码CustomerID外GROUP BY子句的查询相关子查询。 相关子查询会为每一行执行一次返回的GROUP BY子句。 这允许有条款计算每个产品的总量CustomerID从外部查询的值而得到的小计列在每个SalesOrderHeader记录相关的记录CustomerID从外部查询。 清单3中的transact - sql语句,只返回一行CustomerID在购买了超过150000美元的产品。
一个更新语句的例子,其中包含相关子查询
相关子查询不仅可以用于使用SELECT语句返回一个结果集。 您也可以使用它们在SQL服务器更新数据表。 为了证明这一点,我首先会生成一些测试数据下边表,通过使用清单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表,然后填充用八行代表汽车目前在库存。
定期销售经理喜欢看到他InvoicePriceRatio通过运行清单5中的查询
SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio FROM CarInventory;
清单5:InvoicePriceRatio查询
当经理运行该查询,她注意到有许多类似的汽车一样InvoicePrice数量有不同InvoicePriceRatio值。 最大化发票标价比她问她的IT支持编写一个查询,该查询将更新StickerPrice在她所有的汽车每辆车相同CarName 有相同的价值InvoicePriceRatio。 她希望这家伙来设置StickerPrice相同的值作为最高标价CarName。 这样所有的汽车一样CarName都有相同的价值StickerPrice价值。 完成这个更新的CarInventory表,这家伙运行清单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然后使用相关子查询中找到的值来更新StickerPrice值为每个CarInventory有相同的记录CarName。
相关子查询的性能注意事项
有些性能考虑你应该意识到写作时transact - sql语句包含相关子查询。 性能不是坏当外层查询包含一个小的行数。 但当外层查询包含大量的行不规模从性能角度来看。 这是因为相关子查询中的每个候选人需要执行行外部查询。 因此,当外部查询包含越来越多的候选人行相关子查询多次执行,因此transact - sql语句将需要更长的时间。 如果你发现你的相关子查询transact - sql语句的性能没有达到您的需求,那么您应该寻找替代解决方案,比如使用一个内部或外部连接操作的查询,或返回一个小数量的候选人的行从外部查询。
总结
相关子查询是一种内在的查询,包括一个或多个列从外部查询。 相关子查询是每个外部的候选人行执行一次查询。 因为相关子查询包含一个列外查询不能独立运行的外部查询。 相关子查询的地方,虽然没有很好地扩展从性能角度来看当有大量的候选人中确定行外部查询。
问题和答案
在本节中,您可以检查你如何理解相关子查询的概念通过回答下列问题。
问题1
编写相关子查询时需要___________________。 (填入空白)
1、一个或多个列的内部查询用于限制相关子查询的结果。
2、一个或多个列的内部查询所使用的选择列表相关子查询。
3、一个或多个列从外部查询用于限制相关子查询的结果。
4、一个或多个列从外部查询所使用的选择列表相关子查询。
问题2
选择所有的以下相关子查询语句,是正确的
1、随着候选人的行数的增加性能数据库引擎执行的sql语句,其中包含一个相关子查询得到改善。
2、每个候选人的相关子查询会执行一次行从外部查询。
3、相关子查询会引用一个或多个列的内部查询。
4、当使用子查询相关条款内查询将对每个候选人行执行一次返回的GROUP by子句。
问题3:
相关子查询就像一个正常的子查询,和相关子查询可以运行独立于整个transact - sql语句(真或假)。
-
答案:
问题1:
正确答案是c。相关子查询需要的一个或多个列外查询相关子查询语句中使用。 这些外列引用替换为值在执行每个候选人行相关子查询。
问题2:
正确答案是b和d是不正确的,因为随着候选人的行数的增加相关子查询被多次执行,和数据库引擎执行的sql语句的性能恶化。 c是错误的,因为相关子查询必须包含一个或多个行从外部查询,而不是内在的查询。
问题3:
正确的答案是b。如果你尝试运行数据库引擎执行完整的sql语句的独立相关子查询,相关子查询语句将失败。
