注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

T-SQL查询进阶--深入理解子查询  

2012-02-29 22:35:41|  分类: MS SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

引言

 

      SQL有着非常强大且灵活的查询方式,而多表连接操作往往也可以用子查询进行替代,本篇文章将会讲述子查询的方方面面。

 

简介

 

      子查询本质上是嵌套进其他SELECT,UPDATE,INSERT,DELETE语句的一个被限制的SELECT语句,在子查询中,只有下面几个子句可以使用

  1. SELECT子句(必须)
  2. FROM子句(必选)
  3. WHERE子句(可选)
  4. GROUP BY(可选)
  5. HAVING(可选)
  6. ORDER BY(只有在TOP关键字被使用时才可用)

    子查询也可以嵌套在其他子查询中,这个嵌套最多可达32层。子查询也叫内部查询(Inner query)或者内部选择(Inner Select),而包含子查询的查询语句也叫做外部查询(Outter)或者外部选择(Outer Select),子查询的概念可以简单用下图阐述:

1

   

     上图是作为数据源使用的一个子查询.

     通常来讲,子查询按照子查询所返回数据的类型,可以分为三种,分别为:

  1. 返回一张数据表(Table)
  2. 返回一列值(Column)
  3. 返回单个值(Scalar)

     下面,我们按照这三种方式来阐述子查询

 

子查询作为数据源使用

     当子查询在外部查询的FROM子句之后使用时,子查询被当作一个数据源使用,即使这时子查询只返回一个单一值(Scalar)或是一列值(Column),在这里依然可以看作一个特殊的数据源,即一个二维数据表(Table).作为数据源使用的子查询很像一个View(视图),只是这个子查询只是临时存在,并不包含在数据库中。

     比如这个语句:

SELECT     P.ProductID, P.Name, P.ProductNumber, M.Name AS ProductModelName FROM         Production.Product AS P INNER JOIN (SELECT     Name, ProductModelID  FROM          Production.ProductModel) AS M  ON P.ProductModelID = M.ProductModelID

    上述子查询语句将ProductModel表中的子集M,作为数据源(表)和Product表进行内连接。结果如下:

   2

    作为数据源使用也是子查询最简单的应用。当然,当子查询作为数据源使用时,也分为相关子查询无关子查询,这会在文章后面介绍到.

 

子查询作为选择条件使用

 

    作为选择条件的子查询也是子查询相对最复杂的应用.

    作为选择条件的子查询是那些只返回一列(Column)的子查询,如果作为选择条件使用,即使只返回单个值,也可以看作是只有一行一列.比如:

    在AdventureWorks中:

    我想取得总共请病假天数大于68小时的员工:

SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact]   WHERE ContactID IN     (SELECT EmployeeID   FROM [AdventureWorks].[HumanResources].[Employee]   WHERE SickLeaveHours>68)

   结果如下:

   3

 

   上面的查询中,在IN关键字后面的子查询返回一列值作为外部查询选择条件使用.

   同样的,与IN关键字的逻辑取反的NOT IN关键字,这里就不再阐述了

   但是要强调的是,不要用IN和NOT IN关键字,这会引起很多潜在的问题,这篇文章对这个问题有着很好的阐述:http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in。这篇文章的观点是永远不要再用IN和NOT IN关键字,我的观点是存在即合理,我认为只有在IN里面是固定值的时候才可以用IN和NOT IN,比如:

SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact]   WHERE ContactID  IN (25,33)

   只有在上面这种情况下,使用IN和NOT IN关键字才是安全的,其他情况下,最好使用EXISTS,NOT EXISTS,JOIN关键字来进行替代. 除了IN之外,用于选择条件的关键字还有ANYALL,这两个关键字和其字面意思一样. 和"<",">",”="连接使用,比如上面用IN的那个子查询:

   我想取得总共请病假天数大于68小时的员工

   用ANY关键字进行等效的查询为:

SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact]   WHERE ContactID =ANY    (SELECT EmployeeID   FROM [AdventureWorks].[HumanResources].[Employee]   WHERE SickLeaveHours>68)

   在作为ANY和ALL关键字在子查询中使用时,所实现的效果如下

=ANY 和IN等价
<>ALL 和NOT IN等价
>ANY 大于最小的(>MIN)
<ANY 小于最大的(<MAX)
>ALL 大于最大的(>MAX)
<ALL 小于最小的(<MIN)
=ALL 下面说

   =ALL关键字很少使用,这个的效果在子查询中为如果只有一个返回值,则和“=”相等,而如果有多个返回值,结果为空

   这里要注意,SQL是一种很灵活的语言,就像子查询所实现的效果可以使用JOIN来实现一样(效果一样,实现思路不同),ANY和ALL所实现的效果也完 全可以使用其他方式来替代,按照上面表格所示,>ANY和>MIN完全等价,比如下面两个查询语句完全等价:

SELECT * FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>ANY  (SELECT SickLeaveHours FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>68)   SELECT * FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>  (SELECT MIN(SickLeaveHours) FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>68)

 

 

相关子查询和EXISTS关键字

   前面所说的查询都是无关子查询(Uncorrelated subquery),子查询中还有一类很重要的查询是相关子查询(Correlated subquery),也叫重复子查询比如,还是上面那个查询,用相关子查询来写:

   我想取得总共请病假天数大于68天的员工:

SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact] c   WHERE EXISTS    (SELECT *   FROM [AdventureWorks].[HumanResources].[Employee] e   WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)

   结果和使用IN关键字的查询结果相同:

   3

   如何区别相关子查询无关子查询呢?最简单的办法就是直接看子查询本身能否执行,比如上面的例子中的子查询

(SELECT *   FROM [AdventureWorks].[HumanResources].[Employee] e   WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)

   这一句本身执行本身会报错.因为这句引用到了外部查询的表

   对于无关子查询来说,整个查询的过程为子查询只执行一次,然后交给外部查询,比如:

  

SELECT * FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>ANY  SQLRESULT

   上面的无关子查询,整个查询过程可以看作是子查询首先返回SQLResult(SQL结果集),然后交给外部查询使用,整个过程子查询只执行一次

    而相反,作为相关子查询,子查询的执行的次数依赖于外部查询,外部查询每执行一行,子查询执行一次,比如:

    还是上面的例子:我想取得总共请病假天数大于68天的员工

SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact] c   WHERE EXISTS    (SELECT *   FROM [AdventureWorks].[HumanResources].[Employee] e   WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)  ---- step 1: SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact] c   WHERE EXISTS    (SELECT *   FROM [AdventureWorks].[HumanResources].[Employee] e   WHERE 1=e.ContactID AND e.SickLeaveHours>68) ---- step 2: SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact] c   WHERE EXISTS    (SELECT *   FROM [AdventureWorks].[HumanResources].[Employee] e   WHERE 2=e.ContactID AND e.SickLeaveHours>68) ---- step n: SELECT [FirstName]       ,[MiddleName]       ,[LastName]   FROM [AdventureWorks].[Person].[Contact] c   WHERE EXISTS    (SELECT *   FROM [AdventureWorks].[HumanResources].[Employee] e   WHERE n=e.ContactID AND e.SickLeaveHours>68)
 

 

  如上面代码所示。上面的相关子查询实际上会执行N次(N取决与外部查询的行数),外部查询每执行一行,都会将对应行所用的参数传到子查询中,如果子查询有对应值,则返回TRUE(既当前行被选中并在结果中显示),如果没有,则返回FALSE。然后重复执行下一行。

 

子查询作为计算列使用

    当子查询作为计算列使用时,只返回单个值(Scalar) 。用在SELECT语句之后,作为计算列使用。同样分为相关子查询无关子查询

    相关子查询的例子比如:我想取得每件产品的名称和总共的销量

SELECT [Name],       (SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderDetail S       WHERE S.ProductID=P.ProductID) AS SalesAmount FROM [AdventureWorks].[Production].[Product] P   

   部分结果如下:

   5

   当子查询作为计算列使用时,会针对外部查询的每一行,返回唯一的值。

   同样的,SQL子查询都可以使用其他语句达到同样的效果,上面的语句和如下语句达到同样的效果:

SELECT P.Name,COUNT(S.ProductID) FROM [AdventureWorks].[Production].[Product] P  LEFT JOIN  AdventureWorks.Sales.SalesOrderDetail S ON S.ProductID=P.ProductID GROUP BY P.Name   

   子查询作为计算列且作为无关子查询时使用,只会一次性返回但一值,这里就不再阐述了。

 

小结

     本篇文章通过子查询的三种不同用途来阐述子查询。同时,所有的子查询还可以分为相关子查询和无关子查询,而子查询所实现的功能都可以使用连接或者其他方式 实现。但一个好的作家应该是掌握丰富的词汇,而不是仅仅能表达出自己的意思。学会多种SQL查询方式是学习SQL查询必经之路。

转载:http://www.cnblogs.com/CareySon/archive/2011/07/18/2109406.html

  评论这张
 
阅读(898)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017