理解T-SQL:高级查询

时间：2010-09-17 来源：英雄不问出处

1. 子查询

子查询是嵌套在其它查询中常规的SQL查询，当需要一个Select语句作为数据部分的基础或另一个查询中的条件的要素时，就是创建子查询
子查询经常满足下列几种需求：
   a. 把一个查询分解成一系列的逻辑步骤
   b. 提供一个列表作为where子句和[IN|EXSITS|ANY|ALL]的目标对象
   c. 提供由父查询中每一条记录驱动的查找
值得注意的是：大部分子查询可以用联结来表示，在可以使用联结替代子查询的地方，联结通常是更可取的选择。

● 嵌套子查询[nested subquery]
   嵌套子查询只朝一个方向进行--要么返回一个用于外部查询的值，要么返回一个与IN运算符一起使用的值的列表。
   用"="运算符时,返回一个值的查询(从一行返回一列), 用"in"运算符时,返回一个列表.
   语法: Select <Columns> from <Table> where <OneColumn>=(Select <OneColumn> from <OtherTable> where <只返回一行的条件>)
            Select <Columns> from <Table> where <OneColumn> IN (Select <OneColumn> from <OtherTable> where <条件>)
   举例1: 使用单值Select语句的嵌套查询
      假设有两张表:Orders , OrderDetails.
       Orders表中有以下字段:[OrderID],[EmployeeID],[OrderDate],[shippedDate]..
       OrderDetails表中有以下字段: [OrderID],[ProductID],[UnitPrice]..
       现在需要找到OrderDetails表中最早日期的那些订单详细情况.按照一般情况,可以这样做:
          declare @firstDatetime smallDatetime;
          select @firstDatetime=min(OrderDate) from Orders;   --@fistDatetime是返回的一个值.
          select * from Orders join OrderDetails on Orders.OrderID=OrderDetails.OrderID where OrderDate=@firstDatetime;
      但是,有了查询子句,以上三句话可以合并成一句SQL语句实现:
          select * from Orders join OrderDetails on Orders.OrderID=OrderDetails.OrderID where OrderDate=(select min(OrderDate) from Orders);
   举例2: 使用返回多个值的子查询的嵌套查询
       假设有两张表:Employees , Contact
       Employees包括了以下字段:[EmployeeID],[ConactID], [LogInID],[Title] 用来表示雇员的信息
       Contact包括了以下[ConactID],[FirstName],[LastName]表示人员信息
       假设想得到所有职位为Network Manager的人员的名称,除了可以用联结表示外,也可以用子查询:
        select * from HumanResources.Employee E join Person.Contact C on E.ContactID = C.ContactID where E.Title in (select Title from HumanResources.Employee where Title='Network Manager')
        当然,以上只是演示技巧,实际的实现用联结来完成比较好:
        select * from HumanResources.Employee E join Person.Contact C on E.ContactID = C.ContactID where E.Title='Network Manager'
      除了使用IN,还匹配符合的记录,还可以使用NOT IN来排除符合的记录:
         select * from HumanResources.Employee E join Person.Contact C on E.ContactID = C.ContactID where E.Title NOT IN (select Title from HumanResources.Employee where Title='Network Manager')

● ANY,SOME,ALL运算符
    IN和=运算符都是完全匹配记录的,假设想要做的是不是完全匹配,就可以使用ANY,SOME,ALL运算符
    ANY和SOME功能等同,允许在子查询创建的列表上使用范围更广的其它运算符,如>=,<=,<>,!…
    以>为例,>SOME(<列表项>),意味值大于值中的任何一个,即大于一个最小值,因此>SOME(1,2,3)意味着大于1,如果与=使用,则与IN运算符功能相同
    如果是>ALL(1,2,3),则意味着大于其中的所有值,即大于最大值.

2.相关子查询

    相关子查询是那些能够变不可能为可能的事物之一,此外,它经常能把几行代码变成一行,并且常常带来性能提升.相关子查询需要有别于常规的彻底不同的思维方式.
    相关子查询与嵌套子查询不同之处在于:嵌套子查询中,内部查询处理一次,信息被传出用于外部查询.外部的查询也只执行一次. 而相关子查询中,信息是双向进行的.内部查询根据外部查询提供的信息执行,反之亦然.
    相关子查询执行的三个步骤:
       a. 外部查询得到一条记录并传入到内部查询
       b. 内部查询基于传入的值执行
       c. 内部查询从其结果中把值传回到外部查询
举例: 现有一张Orders表,有以下字段:

OrderID     CustomerID    OrderDate                              Freight
----------- ----------     -----------------------          ---------------------
10248       VINET      1996-07-04 00:00:00.000             32.38
10249       TOMSP      1996-07-05 00:00:00.000           11.61
10250       HANAR      1996-07-08 00:00:00.000           65.83
10251       VICTE      1996-07-08 00:00:00.000            41.34
10252       SUPRD      1996-07-09 00:00:00.000           51.30
10253       HANAR      1996-07-10 00:00:00.000           58.17
10254       CHOPS      1996-07-11 00:00:00.000           22.98
10255       RICSU      1996-07-12 00:00:00.000            148.33
10256       WELLI      1996-07-15 00:00:00.000            13.97
10257       HILAA      1996-07-16 00:00:00.000            81.91
10258       ERNSH      1996-07-17 00:00:00.000           140.51
10259       CENTC      1996-07-18 00:00:00.000           3.25
...
    字段分别表示订单号/客户ID/订购日期/运费
    现在我想要知道每个顾客最早一次订单的OrderID信息.
    如果按照我们的想法,找出所有记录中最早订单是这样子的语句:
          select min(orderDate) from Orders; -->是所有订单中最早的订单日期,是一个值
    然后,找出每个顾客的最早订单信息:
          select customerID,min(orderDate) from Orders group by customerID; --以上,Group by语句不能少.是按组进行归纳,得出每个顾客最早的订单日期
    但是,想知道每个顾客最早订单日期的订单号,就没有办法了.使用以下语句是不行的:
          select OrderID,customerID,min(orderDate) from Orders group by customerID; --OrderID不能被customerID进行归纳分组
    所以,解决办法可以使用一个临时表(#MinORderDates),然后分为两个SQL语句,如下:
          select customerID,min(OrderDate) as OrderDate into #MinOrderDates from Orders group by customerID;
         select O.OrderID,O.customerID,O.OrderDate from Orders O join #MinOrderDates M on O.customerID = M.customerID and O.OrderDate=M.OrderDate
     那么,是用相关子查询该怎么解决呢?
        select o1.customerID,o1.OrderID,o1.OrderDate from Orders o1 where o1.orderDate=(select min(o2.orderDate) from Orders o2 where o2.customerID=o1.customerID)
     注意以上语句,子查询中where子句会引用外部查询的表字段.所以,指定别名是必需的.

3. SELECT列表子查询

   子查询也能用来在选择结果中提供一种不同类型的答案,这些情形通常出现在要找寻的信息与查询中的其它数据全然不同的时候,(例如,想要在一个字段上进行聚集,却不希望因此而影响返回的其它字段)
   还是像上一节的查询一样,不过这次需要获得customer的LastName,而不是customerID
    select c.LastName (select MIN(OrderDate) from Orders o where o.customerID=c.customerID) as “OrderDate” from Customers c