数据库第十周作业

通向T-SQL的阶梯：超越基本级别3：构建相关子查询Gregory Larsen著，2014/03/05系列本文是楼梯系列的一部分：楼梯到T-SQL：超越基础从楼梯到T-SQLDML，GregoryLarsen介绍了T-SQL语言的更高级方面，比如子查询。在这个阶梯的第2级中，我讨论了如何在Transact-SQL语句中使用子查询。这个阶梯级别将通过讨论一种称为关联子查询的子查询类型来扩展子查询主题。我将探讨什么是关联子查询，以及它与普通子查询有何不同。此外，我将向您提供一些事务SQL语句的示例，这些语句超出了基础，并使用相关子查询帮助识别结果集中返回的行，以满足复杂的业务需求。什么是关联子查询？在这个阶梯的第2级中，我们了解到普通子查询只是另一个Transact-SQL语句中的SELECT语句，在该语句中，如果独立于外部查询运行，子查询可以返回结果。关联子查询是子查询的一种形式，它不能独立于外部查询运行，因为它包含来自外部查询的一个或多个列。关联子查询与普通子查询一样，有时被称为内部查询。如果关联子查询(内部查询)独立于外部查询运行，它将返回一个错误。由于内部查询的执行取决于外部查询的值，因此称为关联子查询。相关子查询可以执行多次。对于外部查询中选择的每个候选行，它将运行一次。每个候选行的列值将用于为相关子查询的每次执行提供内部外部查询列的值。包含关联子查询的语句的最终结果将基于相关子查询每次执行的结果。相关子查询示例的示例数据为了演示如何使用关联子查询，我需要一些测试数据。我的所有示例都将使用AdventureWorks2008R2数据库，而不是创建自己的测试数据。如果您想继续学习并在您的环境中运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库：http：//msftdbprodsamples.codeplex.com/releases/view/93587WHERE子句中关联子查询的示例为了演示WHERE子句中相关子查询的使用，我希望识别那些在单个订单中购买了70多个商品的CustomerID。为了完成这个需求，我可以运行清单1中的代码。从Sales.SalesOrderHeader OH中选择CustomerID其中(从Sales.SalesOrderDetail中选择count(*)其中SalesOrderID=OH.SalesOrderID)>70；清单1：WHERE子句中的关联子查询当我运行清单1中的代码时，我在报告1中得到了输出。顾客ID29712297223004830107Report 1：运行清单1中的代码时返回的结果如果您查看清单1中的代码，您将看到我通过使用关联子查询限制了我的WHERE。子查询是我从清单1中提取的相关子查询代码中括号内的代码，并将其放在清单2中。从Sales.SalesOrderDetail中选择count(*)其中SalesOrderID=OH.SalesOrderID清单2：清单1中的子查询代码如果您运行清单2中的代码，我会发现我得到了一个错误，显示在报告2中。MSG 4104，第16级，状态1，第3行多部分标识符“OH.SalesOrderID”“不可能被绑起来。报表2：运行清单2中的代码时出错我得到了报告2中显示的错误，因为关联子查询包含对OH.SalesOrderID列的引用，该列是来自外部查询的一列。由于所有相关子查询都引用外部查询中的一个或多个列，因此不能独立于与其关联的外部查询运行它们。不能独立于整个Transact-SQL语句运行子查询，这是关联子查询与普通子查询不同的地方。这里提供的示例是在WHERE子句中使用关联子查询的一个非常简单的示例。希望通过这样一个简单的例子，可以很容易地理解普通子查询和相关子查询之间的区别。通常，相关子查询可能要复杂得多。此外，请记住，在不使用相关子查询的情况下，可能还有其他方法来满足您的业务需求。如您所见，编写关联子查询与普通子查询非常相似，但不能独立于外部查询运行关联子查询。有子句中的关联子查询示例有时，您可能希望通过外部查询的不同值约束HANING子句。这是您可以在HAVING子句中使用关联子查询的时候。假设您必须编写一个查询，该查询将计算那些在2008年购买了价值超过150000美元的产品税前产品的客户的退税金额。清单3中的代码通过在HAVING子句中使用关联子查询来计算这些有价值客户的回扣金额。选择OUTER_H[CustomerID]，SUM(OUT_H.[小计])为TotalPurChase，SUM(OUT_H.[小计])*.10作为回扣来自[销售][SalesOrderHeader]作为外层_H其中年份(外层_H.[订单])=‘2008’由外层_H.[客户ID]组成的组从[Sales].[SalesOrderHeader]中选择SUM(内_H[小计])作为内H其中内_H.[CustomerID]=外层_H.[CustomerID]和年份(内H.[订单日期])=‘2008年’)class=‘150000按退税定单；清单3：有子句的关联子查询当我运行清单5中的代码时，我在报告3中得到了结果。顾客ID全数购货回扣29923 220496.658 22049.66580029641 210647.4929 21064.74929029617 187964.844 18796.48440029913 186387.5613 18638.75613029818 179916.2877 17991.62877029940 175358.3954 17535.83954029987 172169.4612 17216.94612029736 157700.6034 15770.06034029995 156984.5148 15698.45148029770 151824.9944 15182.499440Report 3：运行清单3的结果清单3中的关联子查询代码在关联子查询中的外部查询中使用了来自GROUPBY子句的CustomerID。关联子查询将对GROUPBY子句返回的每一行执行一次。这允许HITH子句通过将每个SalesOrderHeader记录上的小计列的值和外部查询中的CustomerID关联到CustomerID来计算每个CustomerID从外部查询销售到每个CustomerID的产品总量。清单3中的Transact-SQL语句只返回CustomerID购买了价值超过15万美元的产品的一行。包含关联子查询的UPDATE语句示例关联子查询不仅可用于使用SELECT语句返回结果集。还可以使用它们更新SQLServer表中的数据。为了演示这一点，我将首先使用清单4中的代码在temdb表中生成一些测试数据。使用诱饵；去设置NOCOUNT；创建表CarInventory(身份标识，CarName varchar(50)，Vin varchar(50)，十进制(7，2)，十进制(7，2)；去插入CarInventory值(‘Explorer’，‘EXP2014123456A’，46198.45，38201.87)，(“探险家”，“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表，然后用8行填充它，表示当前库存中的汽车。通过运行清单5中的查询，销售经理周期性地喜欢看到他的InvoicePriceRatio。选择CarName，InvoicePrice/StickerPrice*100.0作为Inv