【第4章】复杂查询

本章介绍第 2 章 2.2.3 节“复杂查询”相关的知识和面试题。考查知识点：

y 子查询、临时表 with...as、视图、case 表达式（用于多条件判断）。

4.1子查询

工作中的业务问题有时比较复杂，就需要用子查询来进行复杂查询了。那什么是子查询呢？

子查询就是在from 子句中直接写SQL查询语句，也就是将多个SQL 查询语句嵌套在一起。这个嵌套的 SQL 查询语句就是子查询。

使用子查询时，可以把子查询看作临时表，也就是子查询的查询结果表，因为这个查询结果表并不是真实存放在数据库中的表，所以把这样的表称为临时表。

使用子查询时，一般需要用 as 关键字给子查询起个别名，方便在 SQL 其他地方使用。另外，如果不用 as 关键字给子查询起别名，那么有时候会报错：“1248 - Every derived table must have its own alias”。

通过下面的面试题，我们来学习如何用子查询解决问题。

面试题8：查找成绩排名第二的学生成绩

【题目】

表 4.1 所示“成绩表”记录了学生选修课程的名称及成绩。现在需要找出语文课中成绩排名第二的学生成绩。如果不存在第二名成绩的学生，那么查询应返回 null。

【解题思路】

可以把问题拆解为以下两步。

（1）找出所有选修了语文课的学生的成绩。SQL 语句的书写方法如下：

（2）在语文课的成绩中，找出排名第二的学生成绩。

①考虑到成绩可能有一样的值，所以使用关键字 distinct 对成绩进行去重，SQL 语句的书写方法如下：

②把第①步的查询结果（最高的成绩）记为 a（给子查询用 as 关键字起个别名叫作 a，方便在 SQL 的其他地方使用），然后找出小于 a 的所有成绩，SQL 语句的书写方法如下：

③在小于 a 的所有成绩中，最大值就是课程成绩排在第二名的值。

把上述 SQL 语句合并在一起就是最终答案。需要注意，在条件语句中编写子查询时，不能包含别名。这是因为该子查询会被当作单个值而不是一个表，最终 SQL 语句如下所示：

查询结果如表 4.2 所示。

【本题考点】

（1）汇总函数（最大值 max()）的用法。

（2）去掉重复数据关键字 distinct 的用法。

（3）子查询的用法，子查询经常被当作中间结果的临时表来使用。

嵌套的 SQL 查询语句用括号括起来，叫作子查询。为了方便使用子查询，一般会用 as 关键字给子查询起个别名。

子查询还可以结合逻辑运算符 in、any、all，从而构建复杂的查询。

4.1.1 in（子查询）

in 常用于 where 子句中，表示查询某个范围内的数据。in 和子查询结合在一起的用法是： in（子查询）。

通过下面的面试题，我们来学习如何应用 in（子查询）解决实际问题。

面试题9：如何找出多条件的用户

【题目】

表 4.3 所示的“销售订单表”记录了销售情况，每一条数据表示哪位顾客、哪一天、在哪个交易网点购买了什么产品，购买的数量是多少，以及对应产品的零售价。

现在请查找既购买过 ProductA 产品又购买过 ProductB 产品，但没有购买 ProductC 产品的顾客人数。

【解题思路】

需要把满足以下 3 个条件的顾客查找出来。

（1）购买过 ProductA 产品的顾客。

（2）购买过 ProductB 产品的顾客。

（3）没有购买 ProductC 产品的顾客。

具体步骤如下。

（1）购买过 ProductA 产品的顾客，可以用 in（子查询），SQL 语句的书写方法如下：

（2）购买过 ProductB 产品的顾客，可以用 in（子查询），SQL 语句的书写方法如下：

（3）没有购买 ProductC 产品的顾客，可以在 in 前面加 not，表示不在 in 里面的数据，也就是 not in（子查询），SQL 语句的书写方法如下：

计算满足条件的顾客人数，把上面 3 步的 SQL 语句组合在一起，最终如下：

4.1.2 all（子查询）和 any（子查询）

all（子查询）和 any（子查询）需要和比较运算符，包括“大于（>）”“小于（<）”“不等于 (<>)”等一起使用。

1. all( 子查询 )

all 常用于 where 子句中，表示要满足 all（子查询）里的所有条件。下面通过两个表格：表格 A 和表格 B，来直观地展示如何使用 all（子查询），如图 4.1 所示。

第一种情况，>all( 子查询 )。下面的 SQL 语句表示，表格B 中大于 all( 子查询) 中子查询结果的所有数据，如图 4.2 所示。

第二种情况，<all( 子查询 )。下面的 SQL 语句表示，表格 B 中小于 all( 子查询 ) 中子查询结果的所有数据，如图 4.3 所示。

第三种情况，<>all( 子查询 )。下面的 SQL 语句表示，表格 B 中不等于 all( 子查询 ) 中子查询结果的所有数据。所以，<>all( 子查询 ) 的作用等同于 not in ( 子查询 )，如图 4.4 所示。

等同于：

2. any（子查询）

any 常用于 where 子句中，表示只需满足 ang( 子查询 ) 里的任意一个条件就可以。通过刚才的表格 A 和表格 B 来演示，如图 4.5 所示。

第一种情况，>any( 子查询 )。下面的 SQL 语句表示，表格 B 中大于 any( 子查询 ) 中子查询结果的任意一个数字的数字，等同于表格 B 中大于表格 A 中的最小值（2）的数字， 如图 4.6 所示。

第二种情况，表格 B 中的数字等于 any( 子查询 )（等同于：in( 子查询 )）中子查询结果的任意一个数字，即父查询的结果集满足存在于子查询的结果集中这个条件，如图 4.7 所示。

等同于：

第三种情况，表格 B 中的数字小于 any( 子查询 ) 中子查询结果中的任意一个数字，即父查询的结果集满足小于子查询的任意一个值这个条件，则为真。此处案例要求得到的值小于子查询结果集中的最大值，如图 4.8 所示。

4.2临时表with…as

在实际工作中，有时候业务问题很复杂，这时的 SQL 语句中会嵌套太多子查询，那么SQL 语句的可读性就会变差，有没有好的办法解决这样的问题呢？

答案是用 with...as 语句。

with…as 语句可以将 SQL 语句中的子查询定义为临时表，起到提高 SQL 语句可读性的作用，因此它也被归为子查询部分。

with...as 语句定义临时表的语法如下：

可以看到，一个 with...as 语句中可以定义多个临时表，多个临时表用“,”分隔（注意： with...as 语句定义临时表结束后，不能加语句结束符“;”）。

使用临时表时，可以用 select 语句查询临时表中的数据。例如，4.1.1 节面试题 9 的 SQL 语句如下：

里面有3个子查询，分别是：

我们用 with...as 语句将这 3 个子查询分别定义为临时表 a、b、c。SQL 语句的书写方法如下：

使用 with...as 语句需要注意如下几点。

（1）用 with...as 语句定义的临时表，后面必须直接跟使用该临时表的 SQL 语句，否则临时表将失效，如图 4.9 所示。

（2）用 with...as 语句定义的临时表不需要删除，因为它在创建并使用后即释放，不会真实存放在数据库里。可以理解为，将一条 SQL 语句中的一部分片段封装起来，方便使用。因此， 用 with...as 语