子查询的几种基本实现

时间：2015-11-21 15:53:21 阅读：253 评论：0 收藏：0 [点我收藏+]

一、IN 关键字

　　IN的一般使用方法：value [NOT] IN LIST,一般LIST为与value数据类型相同的一组数据。无[NOT]当LIST中存在与value相同的值时返回true，否则返回false；有[NOT]则相反

LIST 可以为确定的一列值，如：(‘ShangHai‘,‘NanJing‘,‘BeiJing‘)，

也可以是sql的查询结果，但是必须只有一个字段，这点显而易见。如果使用这种方法，就是一种子查询的实现方法了。

示例：查询人力资源部分来自‘Berlin‘或‘Cambridge‘这两个城市的职员的工号

 1 USE AdventureWorks2014
 2 SELECT [BusinessEntityID]
 3 FROM [HumanResources].[Employee]
 4 WHERE [BusinessEntityID] IN (
 5     SELECT [BusinessEntityID]
 6     FROM [Person].[BusinessEntityAddress]
 7     WHERE [AddressID] IN (
 8         SELECT [AddressID]
 9         FROM [Person].[Address]
10         WHERE [City] IN (‘Berlin‘,‘Cambridge‘)
11     )
12 )

查询结果：

BusinessEntityID
1 284
2 288
3 289

二、EXISTS 关键字

　　EXISTS的一般用法：EXISITS (Subqueries)子查询有记录则返回真否则返回假

该语句可放在外查询的WHERE语句中，作为外查询的限制条件

示例：当存在病假时间大于60的职员时，查询该部门的平均病假时间

1 USE AdventureWorks2014
2 SELECT AVG([SickLeaveHours]) AS avg
3 FROM [HumanResources].[Employee]
4 WHERE EXISTS(
5     SELECT *
6     FROM [HumanResources].[Employee]
7     WHERE [SickLeaveHours]>60
8 )

查询结果：

avg

1 45

三、修饰比较符(ALL&ANY)

　　在一般比较符(>、<、=、<>)后加上ALL或ANY。

一般用法：WHERE value >(<、=、<>) ALL(ANY) (Subqueries)

基本逻辑：如果后加ALL,表示value需要与子查询每一行结果做比较运算，且每一次运算结果都为真时，最后结果才是真，任何一比较运算返回假，最后结果为假

如果后加ANY，表示value的任意一次比较返回真，则最后结果为真，全部为假时，最后结果才为假

示例：查询所有比‘Engineering Manager‘职业的所有职员的病假时间都少的职员信息

1 USE AdventureWorks2014
2 SELECT [BusinessEntityID],[SickLeaveHours]
3 FROM [HumanResources].[Employee]
4 WHERE  [SickLeaveHours] <ALL(
5     SELECT [SickLeaveHours]
6     FROM [HumanResources].[Employee]
7     WHERE [JobTitle] = ‘Engineering Manager‘
8 )

查询结果：

BusinessEntityID SickLeaveHours
1 2    20
2 95    20
3 97    20
4 139    20
5 141    20
6 234    20

四、聚合函数

　　常用用法：在内查询中使用包含聚合函数的查询

示例：查询并显示前五条度假时间大于平均值的职员的工号和度假时长

1 SELECT top 5 [BusinessEntityID],[VacationHours]
2 FROM [HumanResources].[Employee]
3 WHERE [VacationHours] > (
4     SELECT AVG([VacationHours])
5     FROM [HumanResources].[Employee]
6     )

查询结果：

　　BusinessEntityID VacationHours
1 　　1    　　　　　　99
2 　　7    　　　　　　61
3 　　8    　　　　　　62
4 　　9    　　　　　　63
5 　　25    　　　　　 64

五、嵌套查询

　　内查询的结果作为外查询的基础，外查询依赖于内查询的结果，T-SQL支持32层嵌套查询（如果数据库主机内存够）

示例：查询email地址为michael0@adventure-works.com的员工的部门号

 1 SELECT [DepartmentID]
 2 FROM [HumanResources].[EmployeeDepartmentHistory]
 3 WHERE [EmployeeID] = (
 4     SELECT [EmployeeID]
 5     FROM [HumanResources].[Employee]
 6     WHERE [ContactID] = (
 7         SELECT [ContactID]
 8         FROM [Person].[Contact]
 9         WHERE [EmailAddress] = ‘michael0@adventure-works.com‘
10     )
11 )

查询结果：

六、关联子查询

　　内查询的结果受外查询的结果限制。在一般的嵌套查询中，内查询只要执行一次；而在关联子查询中，内查询的执行次数与外查询执行次数相同。

这是因为，内查询的查询条件与外查询相关，外查询的不同结果（无限制）会影响内查询的结果（有限制且与外查询相关）。

示例：查询度假时间最长的男职员和女职员的职工号、度假时长和性别

1 SELECT [EmployeeID],[VacationHours],[Gender]
2 FROM [HumanResources].[Employee] e
3 WHERE [VacationHours] = (
4     SELECT MAX([VacationHours])
5     FROM [HumanResources].[Employee]
6     WHERE Gender = e.Gender
7 )

查询结果：

EmployeeID VacationHours Gender
1 109    99    M
2 179    99    M
3 224    99    F

七、应用操作符(Apply)

　　对于apply的操作符的应用一直有些初学者的疑惑，感觉其实现的功能和JOIN...ON差不多，还有点想CROSS JOIN。

　　首先APPLY操作符有两种用法，CROSS APPLY 、 OUTER　APPLY,这和INNER　JOIN 、OUTER JOIN 很相似，实现的功能也基本一样，都是通过笛卡尔乘积联结两个表，不同的是，JOIN(除CROSS JOIN外)均要与ON配合使用，作为联结条件，而APPLY的联结条件是通过WHERE语句来限制的，如果没有WHERE语句也可以执行，类似CROSS　JOIN。所以虽然实现方式不同，但理论上，APPLY实现的操作也可以用JOIN...ON实现。

示例：联结如下两个表，

方式一(CROSS APPLY)：查询拥有贷款账号的客户的姓名、银行账号和贷款账号

方式二(OUTER APPLY)：查询银行客户的姓名、银行账号和贷款账号（无则显示null）

表Depositor:

Customer_name Acc_num
1 Nora    　　101
2 Robin   　  103
3 James   　  107
4 Jennifer    109

表Borrower:

Customer_name Loan_num
1 Nora     301
2 Robin    305
3 James    306
4 Jenne    308

方式一(CROSS APPLY)：

1 SELECT d.Customer_name,d.Acc_num,br.Loan_num FROM Depositor d
2 CROSS APPLY
3 (
4     SELECT *
5     FROM Borrower b 
6     WHERE d.Customer_name = b.Customer_name
7 )br

查询结果：

Customer_name Acc_num Loan_num
1 Nora     101    301
2 Robin    103    305
3 James    107    306

方式二(OUTER APPLY):

1 SELECT d.Customer_name,d.Acc_num,br.Loan_num FROM Depositor d
2 OUTER APPLY
3 (
4     SELECT *
5     FROM Borrower b 
6     WHERE d.Customer_name = b.Customer_name
7 )br

查询结果：

Customer_name Acc_num Loan_num
1 Nora        101    301
2 Robin       103    305
3 James       107    306
4 Jennifer    109    NULL

对应方式一和方式二的JOIN...ON查询方式：

1 SELECT d.Customer_name,Acc_num,Loan_num
2 FROM [dbo].[Depositor] d INNER JOIN [dbo].[Borrower] b ON d.Customer_name = b.Customer_name

1 SELECT d.Customer_name,Acc_num,Loan_num
2 FROM [dbo].[Depositor] d LEFT OUTER JOIN [dbo].[Borrower] b ON d.Customer_name = b.Customer_name

查询结果也是对应相同的。

子查询的几种基本实现

原文：http://www.cnblogs.com/lamulacuo/p/SQL_Subqueries.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)