【T-SQL基础】03.子查询

以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。

本系列【T-SQL基础】主要是针对T-SQL基础的总结。

练习题:

1.写一条查询语句,返回Orders表中活动的最后一天生成的所有订单。

2.查询出拥有订单数量的最多的客户下过的所有订单。

3.查询出2008年5月1号(包括这一天)以后没有处理过订单的雇员。

4.查询2007年下过订单,而在2008年没有下过订单的客户

5.查询定购了第12号产品的客户

概述:

本篇主要是子查询基础的总结。

 关键词解释:

外部查询:查询结果集返回给调用者

内部查询:查询结果集返回给外部查询。

独立子查询:独立子查询独立于其外部查询的子查询,可以单独运行子查询。在逻辑上,独立子查询在执行外部查询之前先执行一次,接着外部查询再使用子查询的结果继续进行查询。

相关子查询:引用了外部查询中出现的表的子查询,查询要依赖于外部查询,不能独立地调用它。在逻辑上,子查询会为每个外部行单独计算一次。

标量子查询:返回单个值的子查询。标量子查询可以出现在外部查询中期望使用单个值的任何地方。

多值子查询:在一个列中

为什么要使用子查询?

可以避免在查询解决方案中把操作分成多个步骤,并在变量中保存中间查询结果的需要。

一、独立子查询

1.独立标量子查询(查看练习题1,2)

例子:从HR.Employees表中返回empid最大的员工信息。

可以分两步:

a.定义一个变量maxid ,通过独立标量子查询查询出empid最大的员工的empid,然后将这个empid保存到变量@maxid中

b.在WHERE条件中过滤出empid = @maxid的记录

DECLARE @maxid AS INT = ( SELECT    MAX(empid)
                          FROM      HR.Employees
                        )
SELECT  *
FROM    HR.Employees
WHERE   empid = @maxid  

更简单的方法是嵌套子查询,只需要一条查询语句就可以查询出empid最大的员工信息

SELECT  *
FROM    HR.Employees
WHERE   empid = ( SELECT    MAX(empid)
                  FROM      HR.Employees
                )

注意:

1.对于有效的标量子查询,它的返回值不能超过一个,如果标量子查询返回了多个值,在运行时则可能会失败。

2.如果标量子查询没有返回任何值,其结果就转换为NULL,和NULL行进行比较得到的是UNKNOWN,查询过滤器不会返回任何让过滤表达式计算结果为UNKNOWN的行。

2.独立多值子查询(查看练习题3)

(1)多值子查询的语法格式

<标量表达式> IN ( <多值子查询> )

例子:返回title包含manager的雇员处理过的订单的信息

方案一:独立多值子查询

SELECT  *
FROM    Sales.Orders
WHERE   empid IN ( SELECT   empid
                   FROM     HR.Employees
                   WHERE    HR.Employees.title LIKE '%Manager' )

方案二:内联接查询

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.首先用独立标量子查询查询出最大的订单日期,返回给外部查询

SELECT  MAX(orderdate)
FROM    sales.Orders AS O2

2.外部查询用O1.orderdate进行过滤,过滤出等于最大订单日期的订单

3.因为要查询出每个客户参与的订单,所以将独立标量子查询改成相关子查询,用子查询O2.custid与外查询O1.custid关联。

对于O1中每一行,子查询负责返回当前客户的最大订单日期。如果O1中某行的订单日期和子查询返回的订单日期匹配,那么O1中的这个订单日期就是当前客户的最大的订单日期,在这种情况下,查询便会返回O1表中的这个行。

SELECT  MAX(orderdate)
FROM    sales.Orders AS O2
WHERE   O2.custid = O1.custid

综合上面的步骤,得到下面的查询语句:

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)

  1. <外查询>  WHERE EXISTS ( 子查询 )
  2. 它的输入是一个子查询,:如果子查询能够返回任何行,改谓词则返回TRUE,否则返回FALSE.
  3. 如果子查询查询结果又多条,SQL SERVER引擎查询出一条记录后,就会立即返回,这种处理方式叫做短路处理。
  4. Exist谓词只关心是否存在匹配行,而不考虑SELECT列表中指定的列,所有使用SELECT * FROM TABLE,并没有什么负面影响,但是为了展开*代码的列名会有少少量的开销,但是还是推荐使用*通配符,查询语句应该尽可能保持自然和直观,除非有非常令人信服的理由,才可以牺牲代码在这方面的要求。
  5. NOT EXISTS谓词是EXISTS谓词的反面

三、练习题

1.写一条查询语句,返回Orders表中活动的最后一天生成的所有订单。

期望结果:

本题考察独立子查询的基本用法,首先用独立子查询返回最后一天的日期,然后外部查询过滤出订单日期等于最后一天的所有订单。

SELECT  orderid ,
        orderdate ,
        custid ,
        empid
FROM    Sales.Orders
WHERE   orderdate = ( SELECT    MAX(orderdate)
                      FROM      Sales.Orders

                    )

2.查询出拥有订单数量的最多的客户下过的所有订单。

期望结果:

本题考察独立子查询的用法,和第一题类似,分两个步骤:

(1)先用子查询查询出订单数量最多的客户id

(2)然后将id返回给外部查询,外部查询通过客户id过滤出客户下过的所有订单

方案一:独立标量子查询

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

                 )

注意:

TOP ( 1 ) WITH TIES O.custid

查找出排序后与第一条记录O.custid相等的所有行

因为下过订单数最多的客户的总订单数是31,且只有一个客户(custid=71),所以最后的查询结果中只有custid=71的客户下过的所有订单。

3.查询出2008年5月1号(包括这一天)以后没有处理过订单的雇员。

期望结果:

本题考察独立子查询的用法,本题也可以采用两步来查询出结果。

(1)首先用子查询返回所有2008年5月1号(包括这一天)以后处理过订单的雇员,将这些雇员的empid返回给外部查询

(2)然后外部查询用NOT IN过滤出所有2008年5月1号(包括这一天)之后没有处理过订单的雇员

方案一:独立标量子查询 + NOT IN

SELECT  *
FROM    HR.Employees
WHERE   empid NOT IN ( SELECT   empid
                       FROM     Sales.Orders
                       WHERE    orderdate >= '20080501' )

4.查询2007年下过订单,而在2008年没有下过订单的客户

期望输出:

方案一:内联接+独立标量子查询

1.查询出20070101~20071231所有下过订单的客户集合Collection1

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

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年下过订单的客户

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年下过订单的客户

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号产品的客户

期望结果:

方案一:内联接多张表

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'

方案二:嵌套相关子查询

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语言基础》

作  者: Jackson0714 出  处:http://www.cnblogs.com/jackson0714/ 关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教! 版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。 特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我 声援博主:您的鼓励是作者坚持原创和持续写作的最大动力! 

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库

渗透测试中常用的SQL命令基础指南

作为渗透测试人员,仅仅利用漏洞攻击目标然后获得一个Shell是远远不够的。尽管拿到Shell是一件很好的事情(毕竟这是我们渗透测试中非常重要的一部分),但是对客...

34680
来自专栏Pythonista

Mysql基础

数据库(database,DB)是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储,具有较小的冗余,较高的数据...

26720
来自专栏大数据和云计算技术

Flink StreamSQL 原理介绍

引言 前面群里面同学说对flink感兴趣,特别邀请资深流专家张如聪给大家深入分析下Flink里面最重要部分:Flink SQL。 本文对Flink SQL深入浅...

1.6K40
来自专栏Java架构沉思录

谈谈Mysql索引优化不得不提防的坑

前言 在之前的文章《聊聊Mysql优化之索引优化》中,笔者简单介绍了Mysql索引优化的原理和一些使用场景,然而Mysql索引优化的内容还远远不止这些。在实际工...

35760
来自专栏数据库

mysql数据操作语句

?imageMogr2/blur/1x0/quality/75|watermark/1/image/aHR0cDovL29zNzhmNGhueS5ia3QuY2...

20550
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战五——SQL查询

Oracle SQL SQL学习是重点,请仔细阅读。 ? O Oracle 结构化查询语言(Structured Query Language)简称SQL(...

35140
来自专栏小灰灰

mysql之基本语法

本篇将主要集中在mysql的使用上,包括如何创建标,如何进行insert,update,select,delete,以及一些常见的sql中关键字的使用姿势

653220
来自专栏GreenLeaves

SQL学习之HAVING过滤分组

1、SQL除了能用Group By分组数据之外,SQL还允许过滤分组,规定包括那些分组,排除那些分组。例如,你可能想要列出至少有两个订单的所有顾客。为此,必须基...

27250
来自专栏社区的朋友们

MySQL 索引与优化

索引是存储引擎内部的一种数据结构,它能让数据库快速地找到需要的值,而不是野蛮地进行自上而下的全表遍历。索引对良好的性能非常关键,尤其是当表中的数据量越来越大时,...

1.1K10
来自专栏学习有记

T-SQL进阶:超越基础 Level 2:编写子查询

11610

扫码关注云+社区

领取腾讯云代金券