sql子查询返回的值不止一个_SQL复杂查询

之前我们已经学习了SQL简单的查询语句，如需请参见以下两篇文章：

一只小猴：SQL简单查询​zhuanlan.zhihu.com

一只小猴：SQL汇总分析​zhuanlan.zhihu.com

本文我们仍首先通过SQLZOO案例来学习，最后再进行基础知识的总结回顾。

（SQLZOO在线练习链接：https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh）

SELECT in SELECT题目详解：

题目一：列出每个国家的名字（name），当中的人口（population）是高于俄罗斯（Russia）的人口。

SELECT name
FROM world
WHERE population > (SELECT population
                    FROM world
                    WHERE name = 'Russia'
                    );

题目二：列出欧洲每国家的人均gdp，当中人均gdp要高于英国（United Kingdom）的数值。

SELECT name
FROM world
WHERE continent = 'Europe' AND gdp/population > (SELECT gdp/population
                                                 FROM world
                                                 WHERE name = 'United Kingdom'
                                                 );

题目三：在阿根廷（Argentina）及澳大利亚（Australia）所在的洲份中，列出当中的国家名字（name）及洲份（continent），按国家名字顺序排序。

SELECT name, continent
FROM world
WHERE continent IN (SELECT continent
                    FROM world
                    WHERE name IN ('Argentina','Australia')
                    )
ORDER BY name;

题目四：哪一个国家的人口比加拿大（Canada）的多，但比波兰（Poland）的少？列出国家名字（name）和人口（population）。

SELECT name, population
FROM world
WHERE population > (SELECT population
                    FROM world
                    WHERE name = 'Canada')
AND population < (SELECT population
                  FROM world
                  WHERE name = 'Poland');

题目五：显示欧洲的国家名称（name）和每个国家的人口（population），以德国的人口的百分比作人口显示。

SELECT name, CONCAT(ROUND(population*100/(SELECT population
                                          FROM world
                                          WHERE name = 'Germany'),0),'%')
FROM world
WHERE continent = 'Europe';

题目六：哪些国家的gdp比欧洲的全部国家都要高呢？（有些国家的记录中，gdp是null，没有填入资料。）

SELECT name
FROM world
WHERE gdp > ALL (SELECT gdp
                 FROM world
                 WHERE continent = 'Europe' AND gdp > 0
                 );

题目七：在每一个洲中找出最大面积的国家，列出洲份（continent）、国家名字（name）及面积（area）。（有些国家的记录中，area是null，没有填入资料。）

SELECT continent, name, area
FROM world AS x
WHERE area >= ALL (SELECT area
                   FROM world AS y
                   WHERE y.continent = x.continent AND area > 0
                   );

题目八：列出洲份名称，和每个洲份中国家名字按字母顺序是排首位的国家名（即每洲只有列一国）。

SELECT continent, name
FROM world AS x
WHERE x.name = (SELECT y.name
                FROM world AS y
                WHERE y.continent = x.continent
                ORDER BY name
                LIMIT 1
                );

题目九：找出洲份，当中全部国家都有少于或等于25000000人口。在这些洲份中，列出国家名字（name）、洲份（continent）和人口（population）。

SELECT name, continent, population
FROM world AS x
WHERE 25000000 >= ALL (SELECT population
                       FROM world AS y
                       WHERE y.continent = x.continent AND population > 0
                       );

题目十：有些国家的人口是同洲份的所有其他国的3倍或以上。列出国家名字（name）和洲份（continent）。

SELECT name, continent
FROM world AS x
WHERE x.population/3 >= ALL (SELECT y.population
                             FROM world AS y
                             WHERE y.continent = x.continent
                             AND population > 0 AND y.name != x.name
                             );

今天的题目是不是比之前的有点难度呢？怎么出现了类似层层嵌套的语句呢？要如何理解呢？如果你还有疑惑，一起来学习相关的知识点吧！

一、视图

从SQL角度来看，视图和表是基本相同的，区别在于：

表中保存的是实际的数据，它会被保存到计算机的存储设备中；

视图中保存的是select语句，它本身并不存储数据，因此可以称视图为临时表，因为只有当客户端与数据库连接且调用视图时，程序才会运行视图中的select语句，而当客户端与数据库断开连接后，此表会被自动删除。

那么视图有什么优点呢？

① 节省存储设备的容量；

② 将频繁使用的select语句保存为视图，可提高工作效率。在某些条件下，当原表中的数据更新后，视图也会相应更新，灵活方便。（具体有哪些条件，后面会提到。）

1. 创建视图

CREATE VIEW 视图名称 (<视图列名1>, <视图列名2>, ......)
AS
<SELECT 查询语句>

注意：select查询语句要写在as关键字后，且其中的排列顺序和视图中列的排列顺序一致。如下例创建“按性别汇总”的视图：

CREATE VIEW summary (gender, number)
AS
SELECT gender, COUNT(*)
FROM student
GROUP BY gender;

2. 使用视图

在from子句中使用视图名称代替表名称，如下：

SELECT gender, number
FROM summary;

使用视图时，避免在视图的基础上创建视图，因为多重视图会降低SQL的性能，所以建议大家尽量使用单一视图。

此外，还要注意视图有两个限制：

① 创建视图不能使用order by子句，因为视图和表一样，数据行是没有顺序的，同时这也并不是通用的语法；

② 当满足以下条件时：select子句中未使用distinct、from子句中只有一张表、未使用group by子句和having子句，视图才能随原表更新而更新。也就是说通过汇总得到的视图无法更新。

3. 删除视图

DROP VIEW 视图名称 (<视图列名1>, <视图列名2>, ......)

二、子查询

子查询就是一次性的视图，就是在一个select查询语句中嵌套了另一个select查询语句，子查询在SQL语句执行完毕就会消失。示例如下：

SELECT gender, number
FROM (
      SELECT gender, COUNT(*)
      FROM student
      GROUP BY gender
      ) AS summary;

它的运行顺序是：

① 先运行内层的查询，即from子句中的select子句；

SELECT gender, COUNT(*)
FROM student
GROUP BY gender;

② 再运行外层的select子句。

SELECT gender, number
FROM summary;

示例中我们将子查询放入了from子句中，其实它还可以放在where子句中与in、any、all一起配合使用，从而构建出复杂的查询条件。其中关键字any、all必须与比较运算符一起使用。

使用子查询时同样避免多层嵌套。

三、标量子查询

标量，意思是单一。因此，标量子查询就是返回单一值的子查询，它的返回值可以用在=或><这样需要单一值的比较运算符中。

一般可以使用单一值的任何位置都可以使用标量子查询，如select子句、where子句、having子句等。示例如下：

-- 用在select子句中
SELECT id, grade, (SELECT AVG(grade)
                   FROM score) AS averagescore
FROM score;

-- 用在where子句中
SELECT id, grade
FROM score
WHERE grade BETWEEN
(SELECT AVG(grade)
 FROM score
 WHERE grade <= 60) AND
(SELECT AVG(grade)
 FROM score
 WHERE grade >= 80);

注意：使用标量子查询时绝对不能返回多行结果。如果返回的是多行结果，它就不是标量子查询了，仅是一个普通的子查询，因此不能与=或><等需要单一输入值的运算符一起使用，也不能用在select子句中。

四、关联子查询

关联子查询在细分的组内进行比较时使用，关联条件一定要写在子查询里面。如我们使用子查询能选取出大于平均成绩学生的学号和成绩，那么稍微改变条件，我们就可选取出每门课程中大于对应课程平均成绩的学生，此时就需要用到关联子查询，如下所示：

SELECT id, course, grade
FROM score AS s1
WHERE grade > (SELECT AVG(grade)
               FROM score AS s2
               WHERE s1.course = s2.course
               GROUP BY course
               );

这里注意关联条件的位置及其作用。想一下下面的代码与上面的代码有什么不同？

SELECT id, course, grade
FROM score AS s1
WHERE grade > (SELECT AVG(grade)
               FROM score AS s2
               GROUP BY course
               );

此代码缺少了关联条件“WHERE s1.course = s2.course”，因此运行时会报错。

因为子查询返回的结果是每门课程的平均值（假设数据表中有语文、数学和英语三门课程），也就是三行结果，此情况就不属于标量子查询，在where子句中使用子查询时，该子查询的结果必须是单一的。

此时，使用关联子查询就可以实现在同一门课程中对每位学生的成绩和平均成绩进行比较。

以上就是视图、子查询、标量子查询、关联子查询的用法。