sql where条件语句用法

这里简单的介绍了where条件语句Transact-SQL
语句都可以改用联接表示。其他问题只能通过子查询提出。在 Transact-SQL
中,包含子查询的语句和语义上等效的不包含子查询的语句在性能上通常没有差别

T-SQL笔记1:SELECT及SELECT高级应用

By Gregory Larsen, 2016/01/01 (首次发布于: 2014/01/29)

SELECT、INSERT、UPDATE 或 DELETE
语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。在此示例中,子查询用作
SELECT 语句中名为 MaxUnitPrice 的列表达式。

 

关于系列

本文属于进阶系列:T-SQL进阶:超越基础

跟随Gregory Larsen的T-SQL
DML进阶系列,其涵盖了更多的高级方面的T-SQL语言,如子查询。

在您开始创建超出基本Transact-SQL语句的更复杂的SQL代码时,您可能会发现需要使用其他SELECT语句的结果来限制查询。
当在父Transact-SQL语句中嵌入SELECT语句时,这些嵌入式SELECT语句被称为子查询或相关子查询。
在“超越基础”楼梯的这个层次上,我将讨论一个子查询的不同方面,在将来的一个层面上,我将讨论相关的子查询。

代码如下复制代码 otherUSE AdventureWorks2008R2;GOSELECT
Ord.SalesOrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM
AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID =
OrdDet.SalesOrderID) AS MaxUnitPriceFROM
AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord

本章摘要

什么是子查询?

子查询只是一个SELECT语句,它包含在另一个Transact-SQL语句中。可以在任何可以使用表达式的地方使用子查询。许多子查询返回单个列值,因为它们与比较运算符(=,!=,<,<=,>,>
=)或表达式结合使用。当子查询不用作表达式或使用比较运算符时,它可以返回多个值。此外,子查询甚至可以在FROM子句或关键字EXISTS中使用时返回多个列和值。

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

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

使用子查询时还需要考虑以下几点:

  • ntext,text和image数据类型不允许从子查询返回
  • ORDER BY子句不能用于子查询,除非使用TOP操作符
  • 使用子查询的视图无法更新
  • COMPUTE和INTO子句不能在子查询中使用

子查询也称为内部查询或内部选择,而包含子查询的语句也称为外部查询或外部选择。

1:安装AdventureWorks

子查询示例数据示例

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

许多包含子查询的 Transact-SQL
语句都可以改用联接表示。其他问题只能通过子查询提出。在 Transact-SQL
中,包含子查询的语句和语义上等效的不包含子查询的语句在性能上通常没有差别。但是,在一些必须检查存在性的情况中,使用联接会产生更好的性能。否则,为确保消除重复值,必须为外部查询的每个结果都处理嵌套查询。所以在这些情况下,联接方式会产生更好的效果。以下示例显示了返回相同结果集的
SELECT 子查询和 SELECT 联接:

2:基本运算符和表达式

返回单个值的子查询的示例

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

代码如下复制代码

3:between

列列表中的子查询

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

  • 返回所有Sales.SalesOrderHeader记录有什么有OrderDate等于“2007-02-19
    00:00:00.000”
  • 通过SalesOrderID命令返回的记录
  • 编号每行返回的最旧的顺序的RowNumber为1,next oldest的RowNumber为2等
  • 结果集需要一个名为TotalOrders的列,需要使用等于“2007-02-19
    00:00:00.000”的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';

other/* SELECT statement built using a subquery. */SELECT NameFROM
AdventureWorks2008R2.Production.ProductWHERE ListPrice = (SELECT
ListPrice FROM AdventureWorks2008R2.Production.Product WHERE Name =
‘Chainring Bolts’ );

4:like

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

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

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

/* SELECT statement built using a join that returns the same result
set. */SELECT Prd1. NameFROM AdventureWorks2008R2.Production.Product AS
Prd1 JOIN AdventureWorks2008R2.Production.Product AS Prd2 ON
(Prd1.ListPrice = Prd2.ListPrice)WHERE Prd2. Name = ‘Chainring Bolts’;

5:escape

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

通过将此子查询列在列列表中,清单1中的此Transact-SQL语句可以对OrderDate为“2007-02-19
00:00:00.000”的SalesOrderHeader行的数量进行计数,并将该信息与详细信息一起返回
有关具有相同OrderDate值的Sales.SalesOrderHeader记录的行信息。

嵌套在外部 SELECT 语句中的子查询包括以下组件:

6:TOP

WHERE子句中子查询的示例

有时你想根据SELECT语句的结果来驱动WHERE子句条件。
当您在WHERE子句中的SELECT语句时,此SELECT语句实际上是一个子查询。
要演示在WHERE子句中使用子查询,假设您需要显示包含购买超大型长袖徽标运动衫的Sales.SalesOrderDetail记录。
清单3中的代码通过使用子查询来满足我的显示要求。

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

包含常规选择列表组件的常规 SELECT 查询。

7:GROUP BY

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

清单3中的子查询位于WHERE条件的右侧。
此子查询标识Product.Product记录的ProductID,其中产品名称为“Long-Sleeve
Logo Jersey,XL”。 此子查询允许我找到具有与“Long-Sleeve Logo
Jersey,XL”的产品名称相关联的ProductID的所有Sales.SalesOrderDetail记录。

包含一个或多个表或视图名称的常规 FROM 子句。

   7.1:GROUP BY ALL

使用子查询来控制TOP条款的示例

使用TOP子句返回的行数可以由表达式控制。
清单5中的代码标识了应该根据TOP子句中的子查询返回的Sales.SalesOrderDetail行的数量。

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

可选的 WHERE 子句。

   7.2:HAVING

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

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

可选的 GROUP BY 子句。

8:SELECT字句技术

子条款示例

为了演示在HAVING子句中使用子查询,假设您具有以下业务要求:

生成包含Sales.SalesOrderHeader.OrderDate和每个日期的订单数量的结果集,其中订单数量超过“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');

可选的 HAVING 子句。

   8.1:使用DISTINCT消除重复值

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

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

子查询的 SELECT 查询总是使用圆括号括起来。它不能包含 COMPUTE 或 FOR
BROWSE 子句,如果同时指定了 TOP 子句,则只能包含 ORDER BY 子句。

   8.2:返回拼接的结果

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

要演示在函数调用中使用子查询,假设您需要显示OrderDate和每个Sales.SalesOrderHeader记录的最大OrderDate之间的天数。
清单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];

子查询可以嵌套在外部 SELECT、INSERT、UPDATE 或 DELETE 语句的 WHERE 或
HAVING
子句内,也可以嵌套在其他子查询内。尽管根据可用内存和查询中其他表达式的复杂程度的不同,嵌套限制也有所不同,但嵌套到
32 层是可能的。个别查询可能不支持 32
层嵌套。任何可以使用表达式的地方都可以使用子查询,只要它返回的是单个值。

   8.3使用INTO字句

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

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

如果某个表只出现在子查询中,而没有出现在外部查询中,那么该表中的列就无法包含在输出中。

9:子查询

返回多个值的子查询的示例

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

包含子查询的语句通常采用以下格式中的一种:

   9.1:子查询类型

FROM子句中的子查询示例

在FROM子句中,通常会标识您的Transact-SQL语句将对其执行的表或表的集合。
每个表提供一组记录,您的查询将用于确定查询的最终结果集。
子查询可以被认为是返回一组记录的查询,因此它可以像FROM表一样在FROM子句中使用。
清单7中的查询显示了我如何在FROM子句中使用子查询。
当在FROM子句中使用子查询时,从子查询生成的结果集通常称为派生表。

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

代码如下复制代码

   9.2:代替表达式的查询

清单7:FROM子句中的子查询
  • 清单7中的代码使用FROM子句中的子查询来创建一个名为Last10SalesOrders的表别名。
    我的子查询返回包含ProductID为716的最后10个Sales.alesOrderDetail记录。
  • 清单7中的代码是一个非常简单的例子,说明如何在FROM子句中使用子查询。
    通过在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

WHERE expression [NOT] IN (subquery)

  9.3:多层嵌套
清单8:使用实际表连接派生表

在清单8中,我看到了我在清单7中创建的子查询/派生表,并将其与SalesOrderHeader表相加。
通过这样做,我可以确定最后10次订购ProductID = 716的OrderDate。

WHERE expression comparison_operator [ANY | ALL] (subquery)

10:比较使用 EXISTS 和 IN 的查询

使用具有IN关键字的子查询的示例

您可以编写一个返回列的多个值的子查询的地方是当您的子查询生成与IN关键字一起使用的记录集时。
清单9中的代码演示了如何使用子查询将值传递给IN关键字。

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

WHERE [NOT] EXISTS (subquery)

11:联接

  11.1:使用衍生表
清单9:使用子查询将值传递给IN关键字

清单9中的代码使用一个子查询从Product.Product表中返回不同的ProductID值,其名称包含字符“XL”。
然后在IN关键字中使用从子查询返回的这些ProductID值来约束从Sales.SalesOrderDetail表返回哪些行。

   11.2:UNION

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

到目前为止,我的所有示例一直在演示如何在SELECT语句的不同部分中使用子查询。
也可以在INSERT,UPDATE或DELETE语句中使用子查询。
清单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;

12:TABLESAMPLE

清单10:INSERT语句中的子查询

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

13:公共表表达式common_table_expression

子查询和JOIN之间的性能考虑

如果您已阅读由Microsoft生成的“子查询基础知识”文档(http://technet.microsoft.com/….aspx)),那么您可能已经在此语句中运行
包含子查询的语句的性能:

“在Transact-SQL中,包含子查询的语句和不具有语义相似的版本的语句通常没有性能差异。

要将使用子查询的查询的性能与不使用子查询的等效查询进行比较,我将在清单3中重写我的子查询以使用JOIN操作。
清单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中的查询相当的JOIN查询

要比较使用子查询的清单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';

1:安装AdventureWorks

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

在运行列表12中的代码之后,我回顾了“SET STATISTICS”语句生成的消息。
通过查看统计信息,我发现这两个查询对SalesOrderDetail表都有3,309个逻辑读取,对于Product表有两个逻辑读取,每个使用31
ms的CPU。 另外我查看了SQL Server为这两个查询创建的执行计划。 我发现SQL
Server为两者生成了相同的执行计划。
因此,对于我的情况使用子查询或JOIN查询产生了等效的性能,正如微软所记录的那样。

    
本系列笔记均基于AdventureWorks数据库,有关AdventureWorks的安装帮助如下:

总结

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

    在 Management Studio
工具栏上,单击“文件”,指向“打开”,然后单击“文件”

问题和答案

在本节中,您可以通过回答以下问题来查看您使用子查询概念了解的内容。

    浏览到文件 instawdb.sql,并单击“打开”。该文件的默认位置为
C:Program FilesMicrosoft SQL
Server90ToolsSamplesAdventureWorks OLTP。

问题1:

完成这个句子“一个子查询是另一个Transact-SQL语句中的SELECT语句,_____________________”。

  • 不能独立于完整的查询运行。
  • 引用来自外部查询的列。
  • 当独立于外部查询运行时,它将返回结果。

    运行脚本之前,在脚本中找到语句
SET @data_path = @sql_path + 'AWDB';,并更改该语句使其指向
instawdb.sql
脚本的位置。例如,SET @data_path = 'C:Program FilesMicrosoft SQL Server90ToolsSamplesAdventureWorks OLTP';

问题2:

什么时候子查询只需要一个列和值才能返回(选择所有适用的)?

  • 当子查询用于FROM子句时
  • 当IN子句中使用子查询时
  • 当表达式中使用子查询时
  • 当子查询与比较运算符一起使用时

    执行脚本。

问题3:

在WHERE子句中使用一个子查询的Transact-SQL语句总是比不包含子查询(True或False)的等效查询执行得慢。

 

回答:

2:基本运算符和表达式

问题1:

正确的答案是c。子查询可以独立于外部查询运行,并返回结果。它不需要来自外部查询的任何列,如果它有来自外部查询的列,它将被称为相关子查询。

运算符 描述
!=
!>
!<
<
<=
<>
=
>
>=
ALL 比较标量值和单列集中的值。
ANY 比较标量值和单列集中的值。SOME 和 ANY 是等效的
BETWEEN 自动根据SQL的型别进行取值
CONTAINS 为单词或短语执行模糊搜索
ESCAPE 指定要以字面值形式搜索,而不是被解释为通配符
EXISTS 指定一个子查询,测试行是否存在
FREETEXT 根据意思,而不是字面值来搜索数据中的单词
IN WHERE color in (‘red’,‘blue’)
IS NOT NULL
IS NULL 检测NULL值
LIKE 根据通配符进行模式匹配
NOT BETWEEN
NOT IN
NOT LIKE
SOME 比较标量值和单列集中的值。SOME 和 ANY 是等效的

问题2:

正确的答案是c和d。当用作表达式或在比较操作中时,子查询需要返回一个列值。当子查询与IN关键字一起使用时,它可以返回列的单个或多个值。如果在FROM子句中使用子查询,它只能返回一列和一个值,但也可以返回多个列和值。

 

问题3:

正确答案是错误的。 SQL
Server优化器非常聪明,很可能为两个等效查询计算相同的执行计划。如果包含子查询的查询的执行计划和没有子查询的查询的执行计划最终都具有相同的执行计划,则两个查询将具有相同的性能。

3:between

    使用:

select SalesOrderID, ShipDate from Sales.SalesOrderHeader
where ShipDate between ‘7/28/2002’ and ‘7/29/2002’

    结果:将会返回17条语句。

    也许有人会用,使用:

where’7/28/2002′ < ShipDate and ShipDate < ‘7/29/2002’

    不行吗?答案是不行。结果会返回0。

   

4:like

     存在如下通配符,

     %:0~N个任意字符;

     _:1个字符;

     []:指定范围或列表中的任何单个字符;

     [^]:指定不再范围中的任何单个字符;

 

5:escape

    where name like ‘b/B%’ escape ‘/’

    解析:表示全部以‘b/B’开头的name,其中/不理解为通配符。

 

6:TOP

    top允许根据定义的行的数量或者百分比查询出开始的N行。如:

    select top 10 from …

    或者:

    declare @percentage float

    set @percentage =1

    select top (@percentage)  percent * from Sales.SalesOrderHeader

 

7:GROUP BY

    指定用来放置输出行的组。如果 SELECT 子句 <select list>
中包含聚合函数,则 GROUP BY 将计算每组的汇总值。

    上面这句话不太好理解,更好的理解应该解释为:

    “由于在SELECT字句中使用了聚合函数,未聚合的列必须出现在GROUP
BY子句中。”

select OrderDate, sum(totalDue) TotalDueByOrderDate from
Sales.SalesOrderHeader
where ShipDate between ‘7/28/2002’ and ‘7/29/2002’
group by OrderDate

    结果:

    (2 行受影响)

   

7.1:GROUP BY ALL

     在上面的代码中,加入ALL,即:

select OrderDate, sum(totalDue) TotalDueByOrderDate from
Sales.SalesOrderHeader
where ShipDate between ‘7/28/2002’ and ‘7/29/2002’
–group by OrderDate
group by all OrderDate

     结果:

Warning: Null value is eliminated by an aggregate or other SET
operation.

(1124 行受影响)

     这说明:ALL包含所有组和结果集,甚至包含那些其中任何行都不满足 WHERE
子句指定的搜索条件的组和结果集。如果指定了
ALL,将对组中不满足搜索条件的汇总列返回空值。

 

7.2:HAVING

     指定组或聚合的搜索条件。HAVING 只能与 SELECT 语句一起使用。HAVING
通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句,则 HAVING 的行为与
WHERE 子句一样。

     相当于对GROUP之前的查询内容进行再一次的条件检索。

     以下示例使用简单 HAVING 子句从 SalesOrderDetail 表中检索超过
$100000.00 的每个 SalesOrderID 的总计。

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
–where ModifiedDate between ‘7/28/2002’ and ‘7/29/2002’
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
–HAVING SalesOrderID = 43875
–HAVING ModifiedDate between ‘7/28/2002’ and ‘7/29/2002’ –error
ORDER BY SalesOrderID ;

 

8:SELECT字句技术

       
SELECT字句技术有很多,除了最简单的拼接等,下面介绍个人认为最有用的。

 

8.1:使用DISTINCT消除重复值

select * FROM HumanResources.Employee

      结果:(290 行受影响)
select DISTINCT HireDate FROM HumanResources.Employee

      结果:(164 行受影响)

      说明已经将重复的抵消了。

 

8.2:返回拼接的结果

DECLARE @Shifts varchar(20)
SET @Shifts = ”
SELECT @Shifts = @Shifts + s.Name + ‘,’ FROM HumanResources.Shift s
SELECT @Shifts

      返回的结果为:Day,Evening,Night,

      这对于我们处理简单的查询并提高效率有很大的好处。

 

8.3使用INTO字句

    INTO字句用来创建新表(对我来说便是备份数据)。

   
一种典型的用法是复制数据到新表(这个新表可以被创建为永久表、临时表或全局临时表),如下代码:

SELECT CustomerID, Name, SalesPersonID, Demographics
INTO  Store_Archive
FROM Sales.Store

    结果:(701 行受影响)

   
说明,1:创建了新表Store_Archive,2:有701行数据被复制到了Store_Archive。

    当然,如果你仅仅想创建新表,而不想复制任何数据,有一个简洁的方法是:

SELECT CustomerID, Name, SalesPersonID, Demographics
INTO  Store_Archive
FROM Sales.Store
WHERE 1=0

 

9:子查询

    子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE
语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

   
联接总是可以表示为子查询。子查询经常(但不总是)可以表示为联接。这是因为联接是对称的:无论以何种顺序联接表
A 和 B,都将得到相同的结果。而对子查询来说,情况则并非如此。

   
使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于,联接使您可以在结果中显示多个表中的列。例如,如果要在结果中包括产品子类别的名称,则必须使用联接版本。

 

9.1:子查询类型

     可以在许多位置指定子查询(必须全部掌握):

  • 使用别名。有关详细信息,请参阅使用别名的子查询.aspx)。
  • 使用 IN 或 NOT IN。有关详细信息,请参阅使用 IN
    的子查询.aspx)和使用
    NOT IN
    的子查询.aspx)。
  • 在 UPDATE、DELETE 和 INSERT 语句中。有关详细信息,请参阅
    UPDATE、DELETE 和 INSERT
    语句中的子查询.aspx)。
  • 使用比较运算符。有关详细信息,请参阅使用比较运算符的子查询.aspx)。
  • 使用 ANY、SOME 或 ALL。有关详细信息,请参阅用 ANY、SOME 或 ALL
    修改的比较运算符.aspx)。
  • 使用 EXISTS 或 NOT EXISTS。有关详细信息,请参阅使用 EXISTS
    的子查询.aspx)和使用
    NOT EXISTS
    的子查询.aspx)。
  • 代替表达式。有关详细信息,请参阅用于替代表达式的子查询.aspx)。

9.2:代替表达式的查询

     必须着重说说代替表达式的子查询。在 Transact-SQL 中,除了在 ORDER BY
列表中以外,在 SELECT、UPDATE、INSERT 和 DELETE
语句中任何能够使用表达式的地方都可以用子查询替代。

    
以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。

USE AdventureWorks;
GO
SELECT Name, ListPrice, 
(SELECT AVG(ListPrice) FROM Production.Product) AS Average, 
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1

 

9.3:多层嵌套

    
子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。

    
以下查询将查找作为销售人员的雇员的姓名。

Use AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact
WHERE ContactID IN
    (SELECT ContactID
     FROM HumanResources.Employee
     WHERE EmployeeID IN
        (SELECT SalesPersonID
         FROM Sales.SalesPerson)

 

10:比较使用 EXISTS 和 IN 的查询

    以下示例比较了两个语义等同的查询。第一个查询使用
EXISTS,第二个查询使用 IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT * 
 FROM HumanResources.Employee AS b
 WHERE a.ContactId = b.ContactID
 AND a.LastName = 'Johnson');
GO

    下面的查询使用 IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
 FROM HumanResources.Employee AS b
 WHERE a.ContactId = b.ContactID
 AND a.LastName = 'Johnson');
GO

    以下是其中任一查询的结果集。

FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)

 

11:联接

   通过联接,可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。

   联接条件中用到的列不必具有相同的名称或相同的数据类型。但如果数据类型不相同,则必须兼容,或者是可由 SQL Server 进行隐式转换的类型。

     联接可分为以下几类:

  • 内部联接(典型的联接运算,使用类似于 = 或 <>
    的比较运算符)。内部联接包括同等联接和自然联接。
    内部联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。例如,检索
    studentscourses 表中学生标识号相同的所有行。
  • 外部联接。外部联接可以是左向外部联接、右向外部联接或完整外部联接。
    在 FROM 子句中可以用下列某一组关键字来指定外部联接:

    • LEFT JOIN 或 LEFT OUTER JOIN。
      左向外部联接的结果集包括 LEFT OUTER
      子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行,则在关联的结果集行中,来自右表的所有选择列表列均为空值。
    • RIGHT JOIN 或 RIGHT OUTER JOIN
      右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行,则将为左表返回空值。
    • FULL JOIN 或 FULL OUTER JOIN
      完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时,另一个表的选择列表列将包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
  • 交叉联接
    交叉联接将返回左表中的所有行。左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。

11.1:使用衍生表

     衍生表是指在FROM字句中作为表的SELECT语句。

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN ( SELECT SalesOrderID
    FROM Sales.SalesOrderDetail
    WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
    s.SalesOrderID = d.SalesOrderID

 

11.2:UNION

   将两个或更多查询的结果合并为单个结果集,该结果集包含联合查询中的所有查询的全部行。

     下面列出了使用 UNION 合并两个查询结果集的基本规则:

  • 所有查询中的列数和列的顺序必须相同。
  • 数据类型必须兼容。

SELECT ProductModelID, Name FROM Production.ProductModel WHERE
ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM
dbo.Gloves ORDER BY Name ;

 

12:TABLESAMPLE

     TABLESAMPLE 子句将从 FROM
子句中的表返回的行数限制到样本数或行数的某一百分比。例如:

SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (10 PERCENT) ;

SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (100 ROWS) ;

 

13:公共表表达式common_table_expression

     指定临时命名的结果集,这些结果集称为公用表表达式
(CTE)。该表达式源自简单查询,并且在单条 SELECT、INSERT、UPDATE 或 DELETE
语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中,作为该语句的
SELECT
定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图