本系列【T-SQL基础】主要是针对T-SQL基础的总结。
练习题:
概述:
本篇主要是子查询基础的总结。
关键词解释:
一、独立子查询
1.独立标量子查询(查看练习题1,2)
例子:从HR.Employees表中返回empid最大的员工信息。
可以分两步:
a.定义一个变量maxid ,通过独立标量子查询查询出empid最大的员工的empid,然后将这个empid保存到变量@maxid中
b.在WHERE条件中过滤出empid = @maxid的记录
1
2
3
4
5
6
|
DECLARE
@maxid
AS
INT
= (
SELECT
MAX
(empid)
FROM
HR.Employees
)
SELECT
*
FROM
HR.Employees
WHERE
empid = @maxid
|
更简单的方法是嵌套子查询,只需要一条查询语句就可以查询出empid最大的员工信息
1
2
3
4
5
|
SELECT
*
FROM
HR.Employees
WHERE
empid = (
SELECT
MAX
(empid)
FROM
HR.Employees
)
|
2.独立多值子查询(查看练习题3)
(1)多值子查询的语法格式
<标量表达式> IN ( <多值子查询> )
例子:返回title包含manager的雇员处理过的订单的信息
方案一:独立多值子查询
1
2
3
4
5
|
SELECT
*
FROM
Sales.Orders
WHERE
empid
IN
(
SELECT
empid
FROM
HR.Employees
WHERE
HR.Employees.title
LIKE
'%Manager'
)
|
方案二:内联接查询
1
2
3
4
|
SELECT
*
FROM
Sales.Orders
INNER
JOIN
HR.Employees
ON
Sales.Orders.empid = HR.Employees.empid
WHERE
HR.Employees.title
LIKE
'%Manager'
|
类似地,很多地方既可以用子查询也可以用联接查询来解决问题。数据库引擎对两种查询的解释有时候是一样的,而在另外一些情况下,对二者的解释则是不同的。可以先用一种查询解决问题,如果性能不行,再尝试用联接替代子查询,或用子查询替代联接。
3.子查询之distinct关键字
当我们想要剔除掉子查询中的重复值时,会想到在子查询中不必指定distinct关键字,其实是没有必要的,因为数据库引擎会帮助我们删除重复的值,而不用我们显示指定distinct关键字。
二、相关子查询
1.相关子查询
什么是相关子查询:引用了外部查询中出现的表的列,依赖于外部查询,不能独立地运行子查询。在逻辑上,子查询会为每个外部行单独计算一次。
例子:查询每个客户返回在他参与活动的最后一天下过的所有订单。
期望结果:
影响行数:90
1.首先用独立标量子查询查询出最大的订单日期,返回给外部查询
1
2
|
SELECT
MAX
(orderdate)
FROM
sales.Orders
AS
O2
|
2.外部查询用O1.orderdate进行过滤,过滤出等于最大订单日期的订单
3.因为要查询出每个客户参与的订单,所以将独立标量子查询改成相关子查询,用子查询O2.custid与外查询O1.custid关联。
对于O1中每一行,子查询负责返回当前客户的最大订单日期。如果O1中某行的订单日期和子查询返回的订单日期匹配,那么O1中的这个订单日期就是当前客户的最大的订单日期,在这种情况下,查询便会返回O1表中的这个行。
1
2
3
|
SELECT
MAX
(orderdate)
FROM
sales.Orders
AS
O2
WHERE
O2.custid = O1.custid
|
综合上面的步骤,得到下面的查询语句:
1
2
3
4
5
6
|
SELECT
orderid,orderdate,custid
FROM
sales.Orders
AS
O1
WHERE
O1.orderdate = (
SELECT
MAX
(orderdate)
FROM
sales.Orders
AS
O2
WHERE
O2.custid = O1.custid
)
|
2.EXISTS谓词(查看练习题4,5)
- <外查询> WHERE EXISTS ( 子查询 )
- 它的输入是一个子查询,:如果子查询能够返回任何行,改谓词则返回TRUE,否则返回FALSE.
- 如果子查询查询结果又多条,SQL SERVER引擎查询出一条记录后,就会立即返回,这种处理方式叫做短路处理。
- Exist谓词只关心是否存在匹配行,而不考虑SELECT列表中指定的列,所有使用SELECT * FROM TABLE,并没有什么负面影响,但是为了展开*代码的列名会有少少量的开销,但是还是推荐使用*通配符,查询语句应该尽可能保持自然和直观,除非有非常令人信服的理由,才可以牺牲代码在这方面的要求。
- NOT EXISTS谓词是EXISTS谓词的反面
三、练习题
1.写一条查询语句,返回Orders表中活动的最后一天生成的所有订单。
期望结果:
本题考察独立子查询的基本用法,首先用独立子查询返回最后一天的日期,然后外部查询过滤出订单日期等于最后一天的所有订单。
1
2
3
4
5
6
7
8
9
|
SELECT
orderid ,
orderdate ,
custid ,
empid
FROM
Sales.Orders
WHERE
orderdate = (
SELECT
MAX
(orderdate)
FROM
Sales.Orders
)
|
2.查询出拥有订单数量的最多的客户下过的所有订单。
期望结果:
本题考察独立子查询的用法,和第一题类似,分两个步骤:
(1)先用子查询查询出订单数量最多的客户id
(2)然后将id返回给外部查询,外部查询通过客户id过滤出客户下过的所有订单
方案一:独立标量子查询
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
custid ,
orderid ,
orderdate ,
empid
FROM
Sales.Orders
WHERE
custid = (
SELECT
TOP
( 1 )
WITH
TIES
O.custid
FROM
Sales.Orders
AS
O
GROUP
BY
custid
ORDER
BY
COUNT
(*)
DESC
)
|
3.查询出2008年5月1号(包括这一天)以后没有处理过订单的雇员。
期望结果:
本题考察独立子查询的用法,本题也可以采用两步来查询出结果。
(1)首先用子查询返回所有2008年5月1号(包括这一天)以后处理过订单的雇员,将这些雇员的empid返回给外部查询
(2)然后外部查询用NOT IN过滤出所有2008年5月1号(包括这一天)之后没有处理过订单的雇员
方案一:独立标量子查询 + NOT IN
1
2
3
4
5
|
SELECT
*
FROM
HR.Employees
WHERE
empid
NOT
IN
(
SELECT
empid
FROM
Sales.Orders
WHERE
orderdate >=
'20080501'
)
|
4.查询2007年下过订单,而在2008年没有下过订单的客户
期望输出:
方案一:内联接+独立标量子查询
1.查询出20070101~20071231所有下过订单的客户集合Collection1
1
2
3
|
SELECT
DISTINCT
C.custid,companyname
FROM
Sales.Orders O
INNER
JOIN
Sales.Customers
AS
C
ON
C.custid = O.custid
WHERE
(orderdate <=
'20071231'
AND
orderdate >=
'20070101'
)
|
2.查询出20080101~20081231所有下过订单的客户结合Collection2
1
2
3
|
SELECT
C.custid
FROM
Sales.Orders O
INNER
JOIN
Sales.Customers
AS
C
ON
C.custid = O.custid
WHERE
(orderdate <=
'20081231'
AND
orderdate >=
'20080101'
)
|
3.Collection1不包含Collection2的子集就是2007年下过订单而在2008年下过订单的客户
1
2
3
4
5
6
7
8
9
|
SELECT
DISTINCT
C.custid,companyname
FROM
Sales.Orders O
INNER
JOIN
Sales.Customers
AS
C
ON
C.custid = O.custid
WHERE
(orderdate <=
'20071231'
AND
orderdate >=
'20070101'
)
AND
C.custid
NOT
IN
(
SELECT
C.custid
FROM
Sales.Orders O
INNER
JOIN
Sales.Customers
AS
C
ON
C.custid = O.custid
WHERE
(orderdate <=
'20081231'
AND
orderdate >=
'20080101'
)
)
|
方案二:相关子查询 EXISTS+NOT EXISTS
1.查询出20070101~20071231所有下过订单的客户集合Collection1
2.查询出20080101~20081231所有下过订单的客户结合Collection2
3.Collection1不包含Collection2的子集就是2007年下过订单而在2008年下过订单的客户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECT
C.custid ,
companyname
FROM
Sales.Customers
AS
C
WHERE
EXISTS (
SELECT
*
FROM
Sales.Orders
AS
O
WHERE
O.custid = C.custid
AND
( orderdate <=
'20071231'
AND
orderdate >=
'20070101'
) )
AND
NOT
EXISTS (
SELECT
*
FROM
Sales.Orders
AS
O
WHERE
O.custid = C.custid
AND
( orderdate <=
'20081231'
AND
orderdate >=
'20080101'
) )
|
由方案一和方案二,我们可以总结出:INNER JOIN+独立子查询可以用Exists+相关子查询代替
5.查询订购了第12号产品的客户
期望结果:
方案一:内联接多张表
1
2
3
4
5
6
7
|
SELECT
DISTINCT
C.custid ,
companyname
FROM
Sales.Customers
AS
C
INNER
JOIN
Sales.Orders
AS
O
ON
C.custid = O.custid
INNER
JOIN
Sales.OrderDetails
AS
D
ON
O.orderid = D.orderid
WHERE
D.productid =
'12'
|
方案二:嵌套相关子查询
1
2
3
4
5
6
7
8
9
10
|
SELECT
C.custid ,
companyname
FROM
Sales.Customers
AS
C
WHERE
EXISTS (
SELECT
*
FROM
Sales.Orders
AS
O
WHERE
O.custid = C.custid
AND
EXISTS (
SELECT
*
FROM
Sales.OrderDetails
AS
D
WHERE
D.orderid = O.orderid
AND
D.productid =
'12'
) )
|
参考资料:
《SQL2008技术内幕:T-SQL语言基础》
原文出自:【T-SQL基础】03.子查询
作 者: Jackson0714
出 处:http://www.cnblogs.com/jackson0714/
关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教!
版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。
特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是作者坚持原创和持续写作的最大动力!