SQL之嵌套查询

1.子查询

子查询（Sub Query）也可以称作嵌套查询（Nested Query）

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

通常来说，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。

简单来说：子查询就是将一个查询（子查询）的结果作为另一个查询（主查询）的数据来源或判断条件的查询。

例如：

1
2
3
4
5
6
7
SELECT *
FROM commodity
WHERE sku_price > (
    SELECT sku_price
    FROM commodity
    WHERE sku_name = '榴莲口红');

上述代码，小括号中的代码为子查询，作用是查询榴莲口红的价格。小括号外的代码被称为主查询，主查询的作用是，查询价格大于榴莲口红的所有商品信息。

注意缩进：为了使代码更清晰易读，在书写时，建议子查询都统一缩进4个空格。

例题：查找薪资高于赵飞的，产品部门的，所有员工信息

1
2
3
4
5
6
SELECT *
FROM employees
WHERE department = '产品' AND salary > (
    SELECT salary
    FROM employees
    WHERE name = '赵飞');

1.1 单行子查询

像前面两个案例一样，只能向外部语句返回一行的子查询称为单行子查询。

1.2 多行子查询

可以向外部语句返回多行（但只有一列）的子查询称为多行子查询。

多行子查询与 IN、ANY、ALL 或 EXISTS 子句一起使用。

1.2.1 NOT IN 子句
1
2
3
4
5
6
SELECT * 
FROM account_2c
WHERE id NOT IN(
   SELECT account_id 
   FROM address
   WHERE city = '成都市');

上述代码的作用是查找客户地址不在成都市的所有客户信息。

小括号中的代码为子查询，作用是查询地址在成都市的所有客户id。

主查询的作用是查询除了地址在成都市的所有客户信息。

在上述查询中，子查询的返回值是“地址不在成都市的用户id”，是一列数据，称之为多行子查询。

联结与子查询：

在很多场景中，使用联结和子查询的方法都可以得到想要的结果，它们各有优劣。

  1. 联结的查询效率比较高；

  2. 子查询的可读性较强，对初学者而言编写思路比较清晰。

1.2.2 ANY 子句
1
2
3
4
5
6
SELECT name
FROM employees
WHERE salary > ANY(
    SELECT salary
    FROM employees
    WHERE department = '设计');

上述代码的作用是查找薪水高于设计部任意同事的所有员工姓名。

小括号中的代码为子查询，作用是查询地址设计部同事的薪资。

主查询的作用是查询薪资高于任意设计部同事薪资的员工姓名。

1.2.3 ALL 子句
1
2
3
4
5
6
SELECT department
FROM employees
WHERE salary > ALL(
    SELECT salary
    FROM employees
    WHERE department = '法务');

上述代码的作用是查找薪酬高于法务部所有同事的所有部门。

小括号中的代码为子查询，作用是查询所有法务部同事的薪资。

主查询的作用是查询薪资高于法务部所有同事薪资的部门。

1.2.4 关联子查询
1
2
3
4
5
6
SELECT * 
FROM employees AS e
WHERE salary >
    (SELECT AVG(salary)
     FROM employees
     WHERE department = e.department);

上述代码的作用是查找出工资大于同部门的平均工资的员工信息。

小括号中的代码为子查询，作用是查询每个部门平均工资。

主查询的作用是查询工资高于平均工资的员工信息。

这里的department = e.department是子查询中引用外查询里的别名。

作用是：外部查询返回的每一行数据，内部查询都要执行一次。

关联子查询的执行过程：

  • 第一步 执行外层查询，也就是表格的全部内容。

    因为子查询中连接了这个表本身（where department = e.department），所以将第一条记录转到子查询。

  • 第二步 进入子查询后，子查询中第一条”陈晶”对应的department是市场.所以先查询出department = ‘市场’的平均工资。 相当于执行

    1
    2
    3
    SELECT AVG(salary)
    FROM employees
    WHERE department='市场'；

  • 第三步 进入外层查询和’陈晶’部门的平均salary相比，高于平均salary的员工信息，相当于执行了：

    1
    2
    3
    SELECT *
    FROM employees
    WHERE salary > 21055.555556 AND department='市场'；

  • 然后抽出第一次外层查询的第二条记录，继续上述三个步骤，直到查询出所有高于部门平均工资的员工信息

1.2.5 总结-子查询的位置：

在上述使用的子查询的案例中，这部分子查询都在WHERE子句后。

此时，子查询的作用是筛选和过滤。另外：

1.子查询也可以放在FROM之后，作为一个临时的表。

例如：

1
2
3
4
SELECT *
FROM 
   (SELECT name,salary,department
    FROM employees) AS a;

查找emplyoees表中的姓名，薪资，部门可以用子查询的写法如上所示

注意：在FROM后的子查询必须要写别名

例题：用子查询的方式，查找account_2b表中的公司名称(company_name)，代理人名称(agent_name)，并将表名取为b

1
2
3
4
SELECT *
FROM
    (SELECT company_name,agent_name
    FROM account_2b) AS b;

2.还可以放在的SELECT之后，作为一个字段。

例如：查找用户名字，地址，在订单表中统计订单数目。可以子查询先计算订单数目作为字段。

1
2
3
4
5
6
SELECT name,
       district,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.account_id = account_2c.id) AS orders
FROM account_2c;

注意：这种子查询的结果只能是单行单列的。

1.2.6 总结-子查询的分类

按照返回结果分类，子查询可以分为：

  • 单行子查询：只能向外部语句返回一行的子查询称为单行子查询

  • 多行子查询：可向外部语句返回多行（但只有一列）的子查询称为多行子查询。

    多行子查询与IN, ANY, ALL, EXISTS子句一起使用。

例题：统计一下没有下订单的客户，其中customers表格中每一行记录了客户的id和名字；indent表格中每一行记录了订单id和对应的客户id。

代码实现：

1
2
3
4
5
SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM indent);

例题：表格film中记录了电影对应的编号，名称等信息；表格category则记录了电影分类的编号，分类名称等信息；表格film_category_1记录了电影与分类的编号对应关系。编写一个SQL语句帮助飞飞查找Action分类的所有电影名称 (title) 和电影描述 (description)

film_category_1表

film_id category_id last_update
1 1 2020-02-14 21:07:09
2 1 2020-02-14 21:07:09
3 6 2020-02-14 21:07:09

film表

film_id title description
1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
2 ACE GOLDFINGER A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China
3 ADAPTATION HOLES A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory

category表

category_id name last_update
1 Action 2020-02-14 20:46:27
2 Animation 2020-02-14 20:46:27
3 Children 2020-02-14 20:46:27
4 Classics 2020-02-14 20:46:27
5 Comedy 2020-02-14 20:46:27
6 Documentary 2020-02-14 20:46:27
7 Drama 2020-02-14 20:46:27

代码实现：

1
2
3
4
5
6
7
8
SELECT title,description
FROM film
WHERE film_id IN (
    SELECT film_id
    FROM film_category_1
    JOIN category
    ON film_category_1.category_id = category.category_id
    WHERE name = 'Action');

例题：activity表格记录了玩家的游戏活动情况。表中每一行记录了某天内某个玩家使用某个设备登陆并玩了0或多个游戏。编写一个SQL语句从activity表格中查找每个玩家 (player_id) 和对应的首次登陆的设备编号 (device_id)

表activity

player_id device_id event_date games_played
1001 2002 2020-03-01 1
1001 2002 2020-03-05 8
1002 2001 2020-06-20 4
1003 2003 2020-05-18 9
1004 2005 2020-03-02 2
1004 2005 2020-04-13 5

代码实现：

1
2
3
4
SELECT player_id,device_id
FROM (SELECT player_id,device_id,MIN(event_date)
     FROM activity
     GROUP BY player_id) AS a;

例题：表格students每一行记录了学生的编号、名字、性别、出生年份、院系、地址信息；表格score每一行记录了分数表编号、学生的编号、科目名称、成绩数据。编写SQL语句从这两个表中查询同时参加了计算机和英语两门考试的学生的所有信息。

表students

id name gender birth department address
901 张老大 男 1999-01-01 计算机系 北京市海淀区
902 张老二 男 1999-01-01 中文系 北京市昌平区
903 张三 女 2000-01-01 中文系 湖南省永州市
904 李四 男 2000-01-01 英语系 辽宁省阜新市
905 王五 女 2001-01-01 英语系 福建省厦门市
906 王六 男 1999-01-01 计算机系 湖南省衡阳市

表score

id stu_id c_name grade
1 901 计算机 98
2 901 英语 80
3 902 计算机 65
4 902 中文 88
5 903 中文 95
6 904 计算机 70
7 904 英语 92
8 905 英语 94
9 906 计算机 90
10 906 英语 85

代码实现：

1
2
3
4
5
6
7
SELECT students.*
FROM students
JOIN score
ON students.id = score.stu_id
WHERE c_name IN ('计算机','英语')
GROUP BY students.id
HAVING COUNT(c_name) > 1;

例题：编写SQL代码查看截止当前，每个用户已经连续签到的天数。假设今天的日期是’2020-04-27’。

表user_attendance

id user_id date is_sign_in
1 1 2020-04-20 1
2 1 2020-04-21 1
3 1 2020-04-22 0
4 1 2020-04-23 0
5 1 2020-04-24 1
6 1 2020-04-25 1
7 1 2020-04-26 1
8 1 2020-04-27 1
9 2 2020-04-20 1
10 2 2020-04-21 1

代码实现：

1
2
3
4
5
SELECT user_id,TIMESTAMPDIFF(day,recent_date,'2020-04-27') AS recent_continuous_days
FROM (SELECT user_id,MAX(date) AS recent_date
     FROM user_attendance
     WHERE is_sign_in = 0
     GROUP BY user_id) AS u;

例题：写一条SQL语句从participants表中查找出既没有拥有最多参与者，也没有最少参与者的活动项目名字 (activity)

表participants

id name activity
1 Bob Eating
2 Mary Singing
3 Melody Singing
4 Shire Eating
5 Jack Eating
6 Janifier Horse Riding

代码实现：

1
2
3
4
5
SELECT activity
FROM participants
GROUP BY activity
HAVING COUNT(*) > ANY( SELECT COUNT(*) FROM participants GROUP BY activity)
       AND COUNT(*) < ANY( SELECT COUNT(*) FROM participants GROUP BY activity);

例题：编写SQL语句从表employee_salaries中查找出员工编号emp_id为10001，其从入职以来的薪水涨幅值growth

表employee_salaries

emp_id salary from_date to_date
10001 185097 2021-06-22 9999-01-01
10001 188958 2020-06-22 2021-06-22
10002 172527 2019-08-03 2020-07-03
10002 172527 2020-08-03 2021-07-03

代码实现：

1
2
3
4
5
6
7
8
9
10
11
SELECT (c2.salary - c1.salary) AS growth
FROM (SELECT *
      FROM employee_salaries
      WHERE emp_id = 10001
      ORDER BY from_date      
      LIMIT 1) AS c1
JOIN (SELECT *
      FROM employee_salaries
      WHERE emp_id = 10001
      ORDER BY from_date DESC     
      LIMIT 1) AS c2;