MySQL 多表查询、连接查询（内连接、外连接）

最新推荐文章于 2024-06-05 17:43:31 发布

Michael阿明

最新推荐文章于 2024-06-05 17:43:31 发布

阅读量2.7k

点赞数 21

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_21201267/article/details/106481752

版权

MySQL 专栏收录该内容

9 篇文章 6 订阅

订阅专栏

文章目录

学习自廖雪峰的官方网站

1. 多表查询

SELECT * FROM <表1> <表2>
SELECT * FROM students, classes;

查询的结果是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回

结果集的列数是两表的列数之和，行数是两表行数之积（要小心，乘积有可能很大）。

对一样的列名，起别名区分（下面的name、id）

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

表也可以起别名，方便简写

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

多表查询也可以使用WHERE条件

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
1班的男生

2. 连接查询

连接查询是另一种类型的多表查询。

连接查询对多个表进行JOIN运算：

先确定一个主表作为结果集
然后，把其他表的行有选择性“连接”在主表结果集上

选出所有学生的信息
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
我们还需要班级的 名称

最常用的一种内连接——INNER JOIN来实现

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s  主表
INNER JOIN classes c  需要连接的表
ON s.class_id = c.id;  ON 条件
可选：加上WHERE子句、ORDER BY等子句

外连接 LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN’
区别：哪边的表的数据完全保留，另一个表的数据不存在的填NULL

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

在这里插入图片描述

练习 LeetCode 175. 组合两个表

题目：

Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255))
Create table Address (AddressId int, PersonId int, City varchar(255), State varchar(255))
Truncate table Person
insert into Person (PersonId, LastName, FirstName) values ('1', 'Wang', 'Allen')
Truncate table Address
insert into Address (AddressId, PersonId, City, State) values ('1', '2', 'New York City', 'New York')

表1: Person
+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键
 
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，
都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

来源：力扣（LeetCode）链接：https://leetcode-cn.com/problems/combine-two-tables
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
SELECT
    P.FirstName, P.LastName, A.City, A.State
FROM Person P
LEFT OUTER JOIN Address A
ON P.PersonId = A.PersonId

369 ms

练习 LeetCode 181. 超过经理收入的员工

题目：

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：
把同一份表再次JOIN该表，条件是A.ManagerId = B.Id

# Write your MySQL query statement below
SELECT A.Name Employee
FROM Employee A
LEFT OUTER JOIN Employee B
ON A.ManagerId = B.Id
WHERE A.Salary > B.Salary

或者

# Write your MySQL query statement below
SELECT A.Name Employee
FROM Employee A
INNER JOIN Employee B
ON A.ManagerId = B.Id
WHERE A.Salary > B.Salary

309 ms

练习 LeetCode 1378. 使用唯一标识码替换员工ID

Employees 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+

id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。

EmployeeUNI 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+

(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。

写一段SQL查询来展示每位用户的唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

你可以以任意顺序返回结果表。

查询结果的格式如下例所示：

Employees table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 7  | Bob      |
| 11 | Meir     |
| 90 | Winston  |
| 3  | Jonathan |
+----+----------+

EmployeeUNI table:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3  | 1         |
| 11 | 2         |
| 90 | 3         |
+----+-----------+

EmployeeUNI table:
+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+

Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/replace-employee-id-with-the-unique-identifier
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select a.unique_id, b.name from
Employees b
left join EmployeeUNI a
on a.id = b.id

练习 LeetCode 1068. 产品销售分析 I

销售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+

(sale_id, year) 是销售表 Sales 的主键.
product_id 是产品表 Product 的外键.
注意: price 表示每单位价格

产品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+

product_id 是表的主键.
写一条SQL 查询语句获取产品表 Product 中所有的产品名称 product name 以及该产品在 Sales 表中相对应的上市年份 year 和价格 price。

示例：

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result 表：
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-sales-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select b.product_name, a.year, a.price
from Sales a
join Product b
on a.product_id = b.product_id

练习 LeetCode 1069. 产品销售分析 II

销售表：Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+

sale_id 是这个表的主键。
product_id 是 Product 表的外键。
请注意价格是每单位的。
产品表：Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+

product_id 是这个表的主键。

编写一个 SQL 查询，按产品 id product_id 来统计每个产品的销售总量。

查询结果格式如下面例子所示:

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result 表：
+--------------+----------------+
| product_id   | total_quantity |
+--------------+----------------+
| 100          | 22             |
| 200          | 15             |
+--------------+----------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-sales-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select s.product_id, sum(s.quantity) total_quantity from
    Sales s left join Product p
    using(product_id)
    group by s.product_id
#   order by s.product_id # 可选排序

练习 LeetCode 1303. 求团队人数

员工表：Employee

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| team_id       | int     |
+---------------+---------+

employee_id 字段是这张表的主键，表中的每一行都包含每个员工的 ID 和他们所属的团队。
编写一个 SQL 查询，以求得每个员工所在团队的总人数。

查询结果中的顺序无特定要求。

查询结果格式示例如下：

Employee Table:
+-------------+------------+
| employee_id | team_id    |
+-------------+------------+
|     1       |     8      |
|     2       |     8      |
|     3       |     8      |
|     4       |     7      |
|     5       |     9      |
|     6       |     9      |
+-------------+------------+
Result table:
+-------------+------------+
| employee_id | team_size  |
+-------------+------------+
|     1       |     3      |
|     2       |     3      |
|     3       |     3      |
|     4       |     1      |
|     5       |     2      |
|     6       |     2      |
+-------------+------------+
ID 为 1、2、3 的员工是 team_id 为 8 的团队的成员，
ID 为 4 的员工是 team_id 为 7 的团队的成员，
ID 为 5、6 的员工是 team_id 为 9 的团队的成员。

来源：力扣（LeetCode）链接：https://leetcode-cn.com/problems/find-the-team-size
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

自连接，按着 team_id，有重复的 team_id，产生了笛卡尔积

# Write your MySQL query statement below
select a.employee_id, count(*) team_size from 
Employee a left join Employee b
on a.team_id = b.team_id # 产生了笛卡尔积
group by a.employee_id

# Write your MySQL query statement below
select a.employee_id, temp.team_size from 
Employee a left join
(
    select team_id, count(*) team_size from Employee
    group by team_id
) temp
# on a.team_id = temp.team_id 也可以用 using
using(team_id)

练习 LeetCode 1350. 院系无效的学生

院系表: Departments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+

id 是该表的主键
该表包含一所大学每个院系的 id 信息

学生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| department_id | int     |
+---------------+---------+

id 是该表的主键
该表包含一所大学每个学生的 id 和他/她就读的院系信息

写一条 SQL 语句以查询那些所在院系不存在的学生的 id 和姓名

可以以任何顺序返回结果

下面是返回结果格式的例子

Departments 表:
+------+--------------------------+
| id   | name                     |
+------+--------------------------+
| 1    | Electrical Engineering   |
| 7    | Computer Engineering     |
| 13   | Bussiness Administration |
+------+--------------------------+

Students 表:
+------+----------+---------------+
| id   | name     | department_id |
+------+----------+---------------+
| 23   | Alice    | 1             |
| 1    | Bob      | 7             |
| 5    | Jennifer | 13            |
| 2    | John     | 14            |
| 4    | Jasmine  | 77            |
| 3    | Steve    | 74            |
| 6    | Luis     | 1             |
| 8    | Jonathan | 7             |
| 7    | Daiana   | 33            |
| 11   | Madelynn | 1             |
+------+----------+---------------+

结果表:
+------+----------+
| id   | name     |
+------+----------+
| 2    | John     |
| 7    | Daiana   |
| 4    | Jasmine  |
| 3    | Steve    |
+------+----------+

John, Daiana, Steve 和 Jasmine 所在的院系
分别是 14, 33, 74 和 77， 
其中 14, 33, 74 和 77 并不存在于院系表

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/students-with-invalid-departments
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select c.id, c.name from
(
    select a.id, a.name, b.name depart_name
    from Students a left join Departments b
    on b.id = a.department_id
) c
where c.depart_name is null # 笛卡尔积，不存在的院系name会为null

select a.id, a.name
from Students a left join Departments b
on b.id = a.department_id
where b.id is null # 笛卡尔积，不存在的院系id会为null

# Write your MySQL query statement below
select a.id, a.name
from Students a
where a.department_id not in (
    select distinct id from Departments
)

练习 LeetCode 613. 直线上的最近距离

表 point 保存了一些点在 x 轴上的坐标，这些坐标都是整数。

写一个查询语句，找到这些点中最近两个点之间的距离。

| x   |
|-----|
| -1  |
| 0   |
| 2   |

最近距离显然是 ‘1’ ，是点 ‘-1’ 和 ‘0’ 之间的距离。所以输出应该如下：

| shortest|
|---------|
| 1       |

注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。

进阶：如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/shortest-distance-in-a-line
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select min(abs(p1.x - p2.x)) shortest 
from point p1 left join point p2
on p1.x != p2.x

练习 LeetCode 1251. 平均售价

Table: Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+

(product_id，start_date，end_date) 是 Prices 表的主键。
Prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。

Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+

UnitsSold 表没有主键，它可能包含重复项。
UnitsSold 表的每一行表示的是每种产品的出售日期，单位和产品 id。

编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。
查询结果格式如下例所示：

Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
 
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+

Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/average-selling-price
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select p.product_id, round(sum(p.price*s.units)/sum(s.units), 2) average_price
from Prices p left join UnitsSold s
on p.product_id = s.product_id 
    and s.purchase_date >= p.start_date
    and s.purchase_date <= p.end_date
group by p.product_id

练习 LeetCode 577. 员工奖金

选出所有 bonus < 1000 的员工的 name 及其 bonus。

Employee 表单

+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+

empId 是这张表单的主关键字

Bonus 表单

+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+

empId 是这张表单的主关键字

输出示例：

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

来源：力扣（LeetCode）链接：https://leetcode-cn.com/problems/employee-bonus
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select e.name, b.bonus 
from Employee e left join Bonus b
using (empId)
where b.bonus < 1000 or b.bonus is null

练习 LeetCode 1327. 列出指定时间段内所有的下单产品

表: Products

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
+------------------+---------+

product_id 是该表主键。
该表包含该公司产品的数据。

表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
+---------------+---------+

该表无主键，可能包含重复行。
product_id 是表单 Products 的外键。
unit 是在日期 order_date 内下单产品的数目。

写一个 SQL 语句，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。

返回结果表单的顺序无要求。

查询结果的格式如下：

Products 表:
+-------------+-----------------------+------------------+
| product_id  | product_name          | product_category |
+-------------+-----------------------+------------------+
| 1           | Leetcode Solutions    | Book             |
| 2           | Jewels of Stringology | Book             |
| 3           | HP                    | Laptop           |
| 4           | Lenovo                | Laptop           |
| 5           | Leetcode Kit          | T-shirt          |
+-------------+-----------------------+------------------+

Orders 表:
+--------------+--------------+----------+
| product_id   | order_date   | unit     |
+--------------+--------------+----------+
| 1            | 2020-02-05   | 60       |
| 1            | 2020-02-10   | 70       |
| 2            | 2020-01-18   | 30       |
| 2            | 2020-02-11   | 80       |
| 3            | 2020-02-17   | 2        |
| 3            | 2020-02-24   | 3        |
| 4            | 2020-03-01   | 20       |
| 4            | 2020-03-04   | 30       |
| 4            | 2020-03-04   | 60       |
| 5            | 2020-02-25   | 50       |
| 5            | 2020-02-27   | 50       |
| 5            | 2020-03-01   | 50       |
+--------------+--------------+----------+

Result 表:
+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+

2020 年 2 月份下单 product_id = 1 的产品的数目总和为 (60 + 70) = 130 。
2020 年 2 月份下单 product_id = 2 的产品的数目总和为 80 。
2020 年 2 月份下单 product_id = 3 的产品的数目总和为 (2 + 3) = 5 。
2020 年 2 月份 product_id = 4 的产品并没有下单。
2020 年 2 月份下单 product_id = 5 的产品的数目总和为 (50 + 50) = 100 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/list-the-products-ordered-in-a-period
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select product_name, unit from
(
    select product_id, sum(unit) unit from Orders
    where(order_date >= '2020-02-01' 
            and order_date < '2020-03-01')
    group by product_id
) t left join Products
using(product_id)
where unit >= 100

练习 LeetCode 603. 连续空余座位

几个朋友来到电影院的售票处，准备预约连续空余座位。

你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |

对于如上样例，你的查询语句应该返回如下结果。

| seat_id |
|---------|
| 3       |
| 4       |
| 5       |

注意：
seat_id 字段是一个自增的整数，free 字段是布尔类型（‘1’ 表示空余， ‘0’ 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/consecutive-available-seats
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select distinct a.seat_id
    from cinema a join cinema b
    on abs(a.seat_id-b.seat_id) = 1 and a.free = true and b.free = true
order by a.seat_id

练习 LeetCode 607. 销售员

给定 3 个表： salesperson， company， orders。
输出所有表 salesperson 中，没有向公司 ‘RED’ 销售任何东西的销售员。

示例：
输入

表： salesperson
+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+
表 salesperson 存储了所有销售员的信息。
每个销售员都有一个销售员编号 sales_id 和他的名字 name 。

表： company
+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+
表 company 存储了所有公司的信息。
每个公司都有一个公司编号 com_id 和它的名字 name 。

表： orders
+----------+------------+---------+----------+--------+
| order_id | order_date | com_id  | sales_id | amount |
+----------+------------+---------+----------+--------+
| 1        |   1/1/2014 |    3    |    4     | 100000 |
| 2        |   2/1/2014 |    4    |    5     | 5000   |
| 3        |   3/1/2014 |    1    |    1     | 50000  |
| 4        |   4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+
表 orders 存储了所有的销售数据，
包括销售员编号 sales_id 和公司编号 com_id 。

输出
+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+
解释
根据表 orders 中的订单 '3' 和 '4' ，
容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。

所以我们需要输出表 salesperson 中所有其他人的名字。

来源：力扣（LeetCode）链接：https://leetcode-cn.com/problems/sales-person
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select name from salesperson
where sales_id not in
(
    select sales_id from
    orders left join company
    using(com_id)
    where company.name = 'RED'
)

练习 LeetCode 1294. 不同国家的天气类型

国家表：Countries

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country_id    | int     |
| country_name  | varchar |
+---------------+---------+
country_id 是这张表的主键。
该表的每行有 country_id 和 country_name 两列。

天气表：Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country_id    | int     |
| weather_state | varchar |
| day           | date    |
+---------------+---------+
(country_id, day) 是该表的复合主键。
该表的每一行记录了某个国家某一天的天气情况。

写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。

天气类型的定义如下：
当 weather_state 的平均值小于或等于15返回 Cold，
当 weather_state 的平均值大于或等于 25 返回 Hot，
否则返回 Warm。

你可以以任意顺序返回你的查询结果。

查询结果格式如下所示：

Countries table:
+------------+--------------+
| country_id | country_name |
+------------+--------------+
| 2          | USA          |
| 3          | Australia    |
| 7          | Peru         |
| 5          | China        |
| 8          | Morocco      |
| 9          | Spain        |
+------------+--------------+
Weather table:
+------------+---------------+------------+
| country_id | weather_state | day        |
+------------+---------------+------------+
| 2          | 15            | 2019-11-01 |
| 2          | 12            | 2019-10-28 |
| 2          | 12            | 2019-10-27 |
| 3          | -2            | 2019-11-10 |
| 3          | 0             | 2019-11-11 |
| 3          | 3             | 2019-11-12 |
| 5          | 16            | 2019-11-07 |
| 5          | 18            | 2019-11-09 |
| 5          | 21            | 2019-11-23 |
| 7          | 25            | 2019-11-28 |
| 7          | 22            | 2019-12-01 |
| 7          | 20            | 2019-12-02 |
| 8          | 25            | 2019-11-05 |
| 8          | 27            | 2019-11-15 |
| 8          | 31            | 2019-11-25 |
| 9          | 7             | 2019-10-23 |
| 9          | 3             | 2019-12-23 |
+------------+---------------+------------+
Result table:
+--------------+--------------+
| country_name | weather_type |
+--------------+--------------+
| USA          | Cold         |
| Austraila    | Cold         |
| Peru         | Hot          |
| China        | Warm         |
| Morocco      | Hot          |
+--------------+--------------+
USA 11 月的平均 weather_state 为 (15) / 1 = 15 
所以天气类型为 Cold。
Australia 11 月的平均 weather_state 为 (-2 + 0 + 3) / 3 = 0.333 
所以天气类型为 Cold。
Peru 11 月的平均 weather_state 为 (25) / 1 = 25
所以天气类型为 Hot。
China 11 月的平均 weather_state 为 (16 + 18 + 21) / 3 = 18.333 
所以天气类型为 Warm。
Morocco 11 月的平均 weather_state 为 (25 + 27 + 31) / 3 = 27.667 
所以天气类型为 Hot。
我们并不知道 Spain 在 11 月的 weather_state 情况所以无需将他包含在结果中。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/weather-type-in-each-country
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解题：

# Write your MySQL query statement below
select country_name, 
        case when avg(weather_state)<= 15 then 'Cold'
             when avg(weather_state)>= 25 then 'Hot'
             else 'Warm' end as weather_type
from Countries left join Weather
using (country_id)
# where day between '2019-11-01' and '2019-11-30'
where day like '2019-11%' # 也可以
group by country_id