翻译（六）——T-SQL的进阶之路：超过基础的2级水平：写子查询

格雷戈里·拉森(Gregory Larsen)，2016/01/01(第一次出版:2014/01/29)

该系列

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

从他的阶梯到T - SQL DML, Gregory Larsen涵盖了T-SQL语言的更高级的方面，如子查询。

当你在开始创建比基本Transact-SQL语句更复杂的SQL代码时，你可能会发现需要使用其他SELECT语句的结果来约束您的查询。当您在父Transact-SQL语句中嵌入SELECT语句时，这些嵌入的SELECT语句被称为子查询或相关子查询。在这个层次的基础上，我将讨论子查询的不同方面，在以后的级别中，我将讨论相关的子查询。

什么是子查询？

一个子查询就是一个包含其他Transact-SQL语句的查询语句，一个子查询可以被用在任何表达式，许多子查询结果返回单个结果因为他们被用在连接比较操作（=，！=，<=,>,>=）或者表达。当子查询不用作表达式或与比较运算符使用时，它可以返回多个值。此外，子查询甚至可以返回多个列和值，当它们在FROM子句或关键字中使用时。

在Transact-SQL语句中很容易发现子查询，因为它将是括号中包含的SELECT语句。由于子查询包含在Transact-SQL语句中，子查询通常被称为内部查询。而包含子查询的Transact-SQL语句被称为外部查询。子查询的另一个特性是，它可以独立于外部查询运行，并且将运行没有错误，并且可能返回一组行，或者返回一个空行集。

子查询的另一种形式是相关子查询。但是关联子查询不能独立于外部Transact SQL语句运行。关联子查询使用来自外部查询的列或列来约束从相关子查询返回的结果。对于本文的相关子查询，这已经足够了。我将在未来的楼梯文章中探索相关的子查询。

以下是使用子查询时需要考虑的其他事项:

ntext、文本和图像数据类型不允许从子查询返回

除非使用顶级运算符，否则不能在子查询中使用ORDER BY子句

不能更新使用子查询的视图

不能在子查询中使用COMPUTE和INTO子句

子查询示例的示例数据

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

返回单个值的子查询示例

如上所述，在比较操作符的一侧使用表达式或返回值的子查询需要返回一个值。Transact SQL语句中有许多不同的地方需要子查询返回一个列值,就像在一个选择列表,where子句等。在本节中,我将提供一系列的例子将演示使用子查询表达式或比较运算符,以满足不同的业务需求。

子查询列列表中

  列表中的子查询是一个SELECT语句，它返回SELECT子句的列列表中的单个列值。为了演示如何在选择列表中使用子查询，我们假设必须从SELECT语句中生成一个具有以下业务需求的结果集:

返回所有的销售数据。SalesOrderHeader记录了一个订单日期等于“2007 -02-19 00:00. 00”

按SalesOrderID顺序订购返回的记录

每一行返回最古老的顺序的行数为1的行数，下一个最大的行数为2，等等

结果集需要一个名为TotalOrders的列，该列需要包含有一个与“2007 -02-19 00:00. 00”相同的OrderDate的订单总数。

满足这些需求的代码如清单1所示。

SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) RowNumber
      , (SELECT COUNT(*) 
         FROM [Sales].[SalesOrderHeader] 
         WHERE ModifiedDate = ‘2007-02-19 00:00:00.000‘) 
                     AS TotalOrders
      , *
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = ‘2007-02-19 00:00:00.000‘;

清单1:列列表中的子查询

在这个Transact - SQL语句中，您可以看到两个不同的SELECT子句。子查询是在清单1的语句中嵌入的SELECT语句，它周围有括号。我已经提取了子查询语句并将其放入清单2中，以防您想要测试验证它是否可以独立于完整的Transact -SQL语句运行。

SELECT COUNT(*) 
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = ‘2007-02-19 00:00:00.000‘

清单2:在清单1中找到的子查询语句

通过在列列表中拥有这个子查询，Listing1中的transact - sql语句能够计算有一个OrderDate“2007 -02-19 00:00. 000”的SalesOrderHeader行的数量，并返回该信息以及关于销售的详细行信息。有相同的OrderDate值的SalesOrderHeader记录。

子查询在WHERE子句中的例子

有时您希望根据SELECT语句的结果来驱动WHERE子句条件。当您在WHERE子句中选择语句时，这个SELECT语句实际上是一个子查询。为了演示在WHERE子句中使用子查询，假设您需要显示销售。SalesOrderDetail包含购买超大的长袖标志运动衫。清单3中的代码使用子查询满足我的显示需求。

SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM [Production].[Product]
                    WHERE Name = ‘Long-Sleeve Logo Jersey, XL‘); 

清单3:WHERE子句中的子查询

清单3中的子查询位于WHERE条件的右侧。这个子查询标识生产的产品。产品名称的产品名称为“长袖标志球衣，XL”。这个子查询允许我找到所有的销售。SalesOrderDetail记录有一个与“长袖Logo Jersey,XL”的产品名称相关的产品。

示例使用子查询来控制TOP子句

使用TOP子句返回的行数可以由表达式控制。清单5中的代码标识了销售数量。SalesOrderDetail行应该基于顶部子句中的子查询返回。

SELECT TOP (SELECT TOP 1 OrderQty 
            FROM [Sales].[SalesOrderDetail]
            ORDER BY ModifiedDate) *  
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 716;

清单4:TOP子句中的子查询

清单4中的代码使用从子查询返回的OrderQty值来标识将在TOP子句中使用的值。通过使用子查询来控制TOP子句返回的行数，可以创建一个子查询，该子查询将动态识别在运行时从查询返回的行数。

以子查询在HAVING子句中的例子举例

为了演示在“有”子句中使用子查询，假设您有以下业务需求:

生成包含sales . salesorderheader的结果集。订单日期和每个日期的订单数量，其中订单数量超过了“2006 -05- 01”的订单数量。

为了满足这一要求，我已经开发了清单6中的查询，其中使用了“HAVING子句”中的子查询。

SELECT count(*), OrderDate 
FROM [Sales].[SalesOrderHeader]
GROUP BY OrderDate
HAVING count(*) >
       (SELECT count(*) 
        FROM [Sales].[SalesOrderHeader]
        WHERE OrderDate = ‘2006-05-01 00:00:00.000‘);

清单5:HAVING子句中的子查询

清单5中的代码在有子句的右边有子查询，并在我的子查询中使用COUNT函数来确定在“2006-05-01”上放置的订单数量。

在函数调用中使用子查询的例子

为了演示在函数调用中使用子查询，假设您有要求在OrderDate和每个Sales的最大OrderDate之间显示天数。SalesOrderHeader记录。清单6中的代码满足这一需求。

SELECT SalesOrderID
      , OrderDate
      ,DATEDIFF
          (
            dd,OrderDate
        ,(SELECT MAX(OrderDate)
          FROM [Sales].[SalesOrderHeader])
          ) AS DaysBetweenOrders
         ,(SELECT MAX(OrderDate)
        FROM [Sales].[SalesOrderHeader]) 
            AS MaxOrderDate
FROM [Sales].[SalesOrderHeader];

清单6:函数调用中的子查询

清单6中的代码有两个不同的子查询。两个子查询都返回Sales中的max OrderDate。SalesOrderHeader表。但是，第一个子查询用于将日期传递给DATEDIFF函数的第二个参数。

返回多个值的子查询示例

到目前为止，我的所有示例都包含子查询，这些子查询仅在单个列中返回单个值。并不是所有的子查询都有这样的要求。接下来的几个示例将使用返回多个值和/或多个列的子查询。

子查询在FROM子句中的示例

在FROM子句中，您通常确定您的transact - sql语句将针对的表或表集合。每个表提供一组记录，您的查询将使用这些记录来确定您的查询的最终结果集。子查询可以看作是返回一组记录的查询，因此它可以像表一样在FROM子句中使用。清单7中的查询显示了我如何在FROM子句中使用子查询。在FROM子句中使用子查询时，子查询生成的结果集通常称为派生表。

SELECT SalesOrderID 
FROM (SELECT TOP 10 SalesOrderID 
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 716
      ORDER BY ModifiedDate DESC) AS Last10SalesOrders;

清单7:FROM子句中的子查询

清单7中的代码使用FROM子句中的子查询创建一个表别名，名为last10 salesorders。我的子查询返回了最后10个销售。alesOrderDetail记录包含一个716的产品。

清单7中的代码是一个非常简单的例子，说明如何在FROM子句中使用子查询。通过使用FROM子句中的子查询，您可以轻松地从语法中构造出更复杂的代码，这些语法将与其他表的子查询结果连接起来，或者添加其他子查询，如清单8所示。

SELECT DISTINCT OrderDate
FROM (SELECT TOP 10 SalesOrderID 
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 716
      ORDER BY ModifiedDate DESC) AS Last10SalesOrders
JOIN [Sales].[SalesOrderHeader] AS SalesOrderHeader
ON Last10SalesOrders.SalesOrderID = SalesOrderHeader.SalesOrderID
ORDER BY OrderDate

清单8:加入一个有实表的派生表

在清单8中，我使用了在清单7中创建的子查询/派生表，并将它与SalesOrderHeader表连接起来。通过这样做，我可以确定一个不同的订单日期，这是最后10次人们订购的产品= 716。

使用IN关键字进行子查询的示例

另一个可以编写子查询的地方，该子查询返回一个列的多个值，当子查询生成一个用于关键字的记录集时。清单9中的代码演示了如何使用子查询将值传递到in关键字。

SELECT * FROM [Sales].[SalesOrderDetail] 
WHERE ProductID IN 
        (SELECT ProductID 
         FROM [Production].[Product]
         WHERE Name like ‘%XL%‘);

清单9:使用子查询将值传递到IN关键字

清单9中的代码使用子查询返回产品的不同值。包含字符“XL”的产品表。这些从子查询返回的ProductID值然后在in关键字中使用，以约束从销售中返回的行。SalesOrderDetail表。

在修改数据的语句中使用子查询的例子

到目前为止，我的所有示例都演示了如何在SELECT语句的不同部分中使用子查询。子查询也可以在插入、更新或删除语句中使用。清单10中的代码展示了如何在INSERT语句中使用子查询。

DECLARE @SQTable TABLE (
OrderID int,
OrderDate datetime,
TotalDue money,
MaxOrderDate datetime);

-- INSERT with SubQuery
INSERT INTO @SQTable 
   SELECT SalesOrderID,
          OrderDate, 
          TotalDue, 
          (SELECT MAX(OrderDate) 
           FROM [Sales].[SalesOrderHeader]) 
   FROM [Sales].[SalesOrderHeader]
   WHERE CustomerID = 29614;

-- Display Records
SELECT * FROM @SQtable;

清单10:插入语句中的子查询

在清单10的代码中，我使用子查询来计算插入到列MaxOrderDate中的值。这仅仅是如何在INSERT语句中使用子查询的一个示例。请记住，子查询也可以在UPDATE和/或DELETE语句中使用。

子查询与连接之间的性能考虑

如果你读过的“子查询基本面”文档由微软(http://technet.microsoft.com/en-us/library/ms189575(v = sql.105). aspx),那么您可能已经运行在这个声明中关于性能包含子查询的语句:

“在transact - sql中，包含子查询和语义等价的语句之间通常没有性能差异。”

为了比较使用子查询和不使用子查询的查询的性能，我将重写清单3中的子查询以使用连接操作。清单11显示了我重新编写的JOIN查询，它相当于清单3中的查询。

SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = ‘Long-Sleeve Logo Jersey, XL‘;

清单11:连接查询，与清单3中的查询等效

为了比较清单3中使用子查询的查询的性能和使用JOIN的清单11中的查询，我将使用清单12中的代码运行两个查询。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Listing 3 query
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM Production.Product
                    WHERE Name = ‘Long-Sleeve Logo Jersey, XL‘); 

-- Listing 11 query
SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = ‘Long-Sleeve Logo Jersey, XL‘;

清单12:测试清单3和清单4的性能的代码

在运行清单12中的代码后，我查看了由“SET STATISTICS”语句生成的消息。通过查看统计数据，我发现两个查询对SalesOrderDetail表有3,309个逻辑读取，2个逻辑读针对产品表，每个都使用31毫秒的CPU。另外，我回顾了为这两个查询创建的SQL Server的执行计划。我发现SQL Server对这两种情况都产生了相同的执行计划。因此，在我的情况下使用子查询或连接查询产生等价的性能，就像微软所记录的那样。

摘要

子查询是嵌入另一个transact - sql语句的SELECT语句。子查询可以独立于外部查询运行，因此有时被称为独立查询。请记住，每当有一个子查询替代表达式时，或者它与比较运算符一起使用，它只能返回单个列和值。通常可以使用JOIN逻辑重写子查询。子查询是帮助您构建更复杂的transact - sql语句以满足业务需求的强大工具。

原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/104517/

翻译（六）——T-SQL的进阶之路：超过基础的2级水平：写子查询

标签：isp   set   添加   举例   pre   library   生成   自己   文章