leetcode: SQL入门

<花开花落>

已于 2022-04-17 17:50:32 修改

阅读量1.2k

点赞数

分类专栏：刷题 MySQL 文章标签： mysql

于 2022-04-17 17:42:02 首次发布

本文链接：https://blog.csdn.net/qq_31362767/article/details/124233113

版权

MySQL 同时被 2 个专栏收录

18 篇文章 5 订阅

订阅专栏

刷题

11 篇文章 0 订阅

订阅专栏

文章目录

组合两个表

表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

解答：

select firstName, lastName, city, state
from Person as a
left join Address as b on (a.PersonId = b.PersonId);

可回收且低脂的产品

表：Products
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+
product_id 是这个表的主键。
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。
写出 SQL 语句，查找既是低脂又是可回收的产品编号。返回结果 无顺序要求。

Products 表：
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+
Result 表：
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+
只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。

解答：

select product_id from Products where low_fats = 'Y' and recyclable = 'Y';

类似题目：大的国家

select name, population, area
from World
where area >= 3000000 or population >= 25000000;

寻找用户推荐人

知识点：比较，null

给定表 customer ，里面保存了所有客户信息和他们的推荐人。
+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。
对于上面的示例数据，结果为：

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

解答：

select name from customer 
where referee_id is null or referee_id != 2 ;
-- where referee_id is null or referee_id <> 2 ;

解释：

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。

任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身，这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

因此，在 WHERE 语句中我们需要做一个额外的条件判断 `referee_id IS NULL’。

从不订购的客户

知识点：多表查询，not in，left join，not exists

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解答：

select Name as Customers 
from Customers
where Customers.Id not in (select CustomerId from Orders);

或者

select Name as Customers 
from Customers as c
left join Orders as o on (c.Id = o.CustomerId) 
where o.CustomerId is null;

或者

select name as Customers
from Customers as c
where not exists (
	select customerId from Orders as o
    where c.Id = o.customerId
);

计算特殊奖金

知识点：mod，left(string, num), if, case, like, regexp

表: Employees
+-------------+---------+
| 列名        | 类型     |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id 是这个表的主键。此表的每一行给出了雇员id ，名字和薪水。

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。返回的结果集请按照employee_id排序。

示例
输入：Employees 表:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
输出：
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
解释：因为雇员id是偶数，所以雇员id 是2和8的两个雇员得到的奖金是0。雇员id为3的因为他的名字以'M'开头，所以，奖金是0。其他的雇员得到了百分之百的奖金。

解答：

select employee_id, 
case when (employee_id % 2 = 1 and left(name, 1) != 'M') then salary else 0 end as bonus
from Employees;

或者

select employee_id, 
if (mod(employee_id, 2) and left(name, 1) != 'M', salary, 0) as bonus
from Employees;

或者

select employee_id, 
case when (mod(employee_id, 2) and name not like 'M%') then salary else 0 end as bonus
-- case when (mod(employee_id, 2) and name regexp '^[^M]') then salary else 0 end as bonus
from Employees;

变更性别

知识点：case，if

Salary 表：
+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id 是这个表的主键。sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。查询结果如下例所示。

示例 1:
输入：
Salary 表：
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
+----+------+-----+--------+
输出：
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |
+----+------+-----+--------+
解释：
(1, A) 和 (3, C) 从 'm' 变为 'f'，(2, B) 和 (4, D) 从 'f' 变为 'm' 。

解答：

update Salary set sex = case when sex = 'm' then 'f' else 'm' end;

或者

update Salary set sex=if(sex="m","f","m");

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

删除重复的电子邮箱

知识点：去重，自连接，delete

表: Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id是该表的主键列。该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

编写一个SQL查询来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。以 任意顺序 返回结果表。查询结果格式如下所示。

示例 1:
输入: 
Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。

解答：

delete p1 from Person p1, Person p2
where p1.email = p2.email and p1.id > p2.id;

用了自连接的方式。在实际生产中，面对千万上亿级别的数据，连接的效率往往最高，因为用到索引的概率较高。

对于 DELETE p1

在 DELETE官方文档中，给出了这一用法，比如下面这个DELETE语句

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

这种DELETE方式很陌生，竟然和SELETE的写法类似。它涉及到t1和t2两张表，DELETE t1表示要删除t1的一些记录，具体删哪些，就看WHERE条件，满足就删；这里删的是t1表中，跟t2匹配不上的那些记录。

对于 p1.Id > p2.Id

从驱动表（左表）取出N条记录；
拿着这N条记录，依次到被驱动表（右表）查找满足WHERE条件的记录；

具体化：

a. 从表p1取出3条记录；
b. 拿着第1条记录去表p2查找满足WHERE的记录，代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后，发现没有满足的，所以不用删掉记录1；
c. 记录2同理；
d. 拿着第3条记录去表p2查找满足WHERE的记录，发现有一条记录满足，所以要从p1删掉记录3；
e. 3条记录遍历完，删掉了1条记录，这个DELETE也就结束了。

修复表中的名字

知识点：upper，lower

表： Users
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键。该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。返回按 user_id 排序的结果表。查询结果格式示例如下。

输入：Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
输出：
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

解答：

select user_id, concat(upper(left(name, 1)), lower(right(name, length(name)-1))) as name 
from Users order by user_id;

select user_id, concat(upper(left(name, 1)), substring(lower(name), 2)) as name 
from Users order by user_id;

-- concat(str1, str2)：字符连接函数
-- upper(str)：将字符串改为大写字母
-- lower(str)：将字符串改为小写字母
-- length(str)：判定字符串长度
-- substring(str, a, b):提取字段中的一段，从字符串str的第a位开始提取，提取b个字符
-- left(str, n)：提取字符串最左边的n个字符
-- right(str, n)：提取字符串最右边的n个字符

按日期分组销售产品

知识点：count，group_concat，separator，distinct, 排序

表 Activities：
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
此表没有主键，它可能包含重复项。此表的每一行都包含产品名称和在市场上销售的日期。

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。每个日期的销售产品名称应按词典序排列。返回按 sell_date 排序的结果表。查询结果格式如下例所示。

示例 1:

输入：Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。

解答：

select sell_date, count(distinct(product)) as num_sold, group_concat(distinct product) as products
from activities
group by sell_date
order by sell_date;

或者

select sell_date, 
	count(distinct(product)) as num_sold, 
	group_concat(distinct product 
                 order by product asc -- 升序排列
                 separator ',')   -- ,分隔
                 as products  -- 组内拼接
from activities
group by sell_date
order by sell_date;

官方文档：group_concat

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

患某种疾病的患者

知识点：like

患者信息表： Patients
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
patient_id （患者 ID）是该表的主键。'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。按 任意顺序 返回结果表。

示例 1:
输入：Patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | SADIAB100   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
解释：Bob 和 George 都患有代码以 DIAB1 开头的疾病。

解决：

select patient_id, patient_name, conditions 
from Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%';

或者

select * from patients where conditions rlike '^DIAB1|\\sDIAB1';  -- '^DIAB1|.*\\sDIAB1'
-- \s表示空格类符号，\\s Mysql里要用双反斜杠来代表转义，第一个"\"来识别符号，再把剩下的"\s"表示正则表达式
-- .代表匹配出\n字符外的所有字符，*和+号都属于贪婪匹配，?属于非贪婪匹配

丢失信息的雇员

知识点：union，

表: Employees
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名。
表: Salaries
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id 是这个表的主键。每一行表示雇员的id 和他的薪水。

写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：雇员的姓名丢失了，或者雇员的薪水信息 丢失了。返回这些雇员的id，employee_id，从小到大排序 。

输入：Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。1号雇员的姓名丢失了。2号雇员的薪水信息丢失了。

解决:

# 先筛选，再合并
select e.employee_id 
	from Employees as e 
	left join Salaries as s on (e.employee_id = s.employee_id) 
	where salary is null
union
select s.employee_id 
	from Salaries as s 
	left join Employees as e on (e.employee_id = s.employee_id) 
	where name is null
order by employee_id;

分解：

mysql> select * from employees
    -> left join salaries on employees.employee_id = salaries.employee_id;
+-------------+----------+-------------+--------+
| employee_id | name     | employee_id | salary |
+-------------+----------+-------------+--------+
|           2 | Crew     |        NULL |   NULL |  -- 再使用 where salary is null 过滤出来，无法使用 employee_id 过滤
|           4 | Haven    |           4 |  63539 |
|           5 | Kristian |           5 |  76071 |
+-------------+----------+-------------+--------+

mysql> select * from salaries
    -> left join employees on salaries.employee_id = employees.employee_id;
+-------------+--------+-------------+----------+
| employee_id | salary | employee_id | name     |
+-------------+--------+-------------+----------+
|           5 |  76071 |           5 | Kristian |
|           1 |  22517 |        NULL | NULL     |  -- 再使用 where name is null 过滤出来
|           4 |  63539 |           4 | Haven    |
+-------------+--------+-------------+----------+

或者

-- 先连接两个表，再筛选
select employee_id 
from 
(
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having count(employee_id) = 1
order by employee_id;

或者

select employee_id from Employees
where employee_id not in (select employee_id from Salaries)
union
select employee_id  from Salaries
where employee_id  not in (select employee_id from Employees)
order by employee_id;

每个产品在不同商店的价格

知识点：union，行列转换

表：Products
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
这张表的主键是product_id（产品Id）。每行存储了这一产品在不同商店store1, store2, store3的价格。如果这一产品在商店里没有出售，则值将为null。请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。输出结果表中的 顺序不作要求。

示例 1：输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：产品0在store1，store2,store3的价格分别为95,100,105。产品1在store1，store3的价格分别为70,80。在store2无法买到。

解决：

select product_id, 'store1' as store, store1 as price from Products where store1 is not null
union
select product_id, 'store2' as store, store2 as price from Products where store2 is not null
union
select product_id, 'store3' as store, store3 as price from Products where store3 is not null
group by product_id order by product_id;

UNION ALL 不会对结果去重，效率比 UNION 更高。如果结果集中存在重复数据建议使用 UNION。

拓展：行列转换

name	subject	score
小明	语文	91
小明	数学	92
小明	英语	93
大黄	语文	95
大黄	数学	96
大黄	英语	97

name	语文	数学	英语
小明	91	92	93
大黄	95	96	97

Create table If Not Exists StudentScore (name text, subject text, score int);
insert into StudentScore (name, subject, score) values ('小明', '语文', 91);
insert into StudentScore (name, subject, score) values ('小明', '数学', 92);
insert into StudentScore (name, subject, score) values ('小明', '英语', 93);
insert into StudentScore (name, subject, score) values ('大黄', '语文', 95);
insert into StudentScore (name, subject, score) values ('大黄', '数学', 96);
insert into StudentScore (name, subject, score) values ('大黄', '英语', 97);

-- 表1转为表2
select name, 
max(case when subject = '语文' then score else 0 end) as '语文', 
max(case when subject = '数学' then score else 0 end) as '数学',
max(case when subject = '英语' then score else 0 end) as '英语'
from StudentScore group by name;

+------+------+------+------+
| name | 语文 | 数学 | 英语 |
+------+------+------+------+
| 小明 |   91 |   92 |   93 |
| 大黄 |   95 |   96 |   97 |
+------+------+------+------+

Create table If Not Exists StudentScore2 (name text, chinese text, math text, english text);
insert into StudentScore2 (name, chinese, math, english) values ('小明', 91, 92, 93);
insert into StudentScore2 (name, chinese, math, english) values ('大黄', 95, 96, 97);

-- 表2转为表1
select name, 'chinese' as subject, chinese as score from StudentScore2
union 
select name, 'math' as subject, math as score from StudentScore2
union
select name, 'english' as subject, english as score from StudentScore2
group by name order by name; 

+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 大黄 | chinese | 95    |
| 大黄 | math    | 96    |
| 大黄 | english | 97    |
| 小明 | chinese | 91    |
| 小明 | math    | 92    |
| 小明 | english | 93    |
+------+---------+-------+

树节点

知识点：树，case

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
树中每个节点属于以下三种类型之一：
叶子：如果这个节点没有任何孩子节点；根：如果这个节点是整棵树的根，即没有父节点；内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+
解释
节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。
节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。
节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。
样例中树的形态如下：
			  1
			/   \
	       2     3
	     /   \
	    4     5
注意，如果树中只有一个节点，你只需要输出它的根属性。

解决：

select id, 
	case when p_id is null then 'Root' 
		 when (id in (select p_id from tree)) then 'Inner' 
		 else 'Leaf' end as 'type'
from tree;

或者

select id, 
    if(p_id is null, 'Root', 
       if(id in (select p_id from tree), 'Inner','Leaf')) as 'type'
from tree;

进店却未进行过交易的顾客

知识点：表连接，过滤筛选

表：Visits
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id 是该表的主键。该表包含有关光临过购物中心的顾客的信息。

表：Transactions
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id 是此表的主键。此表包含 visit_id 期间进行的交易的信息。

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。返回以 任何顺序 排序的结果表。查询结果格式如下例所示。

输入:Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1        | 23          |
| 2        | 9           |
| 4        | 30          |
| 5        | 54          |
| 6        | 96          |
| 7        | 54          |
| 8        | 54          |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2              | 5        | 310    |
| 3              | 5        | 300    |
| 9              | 5        | 200    |
| 12             | 1        | 910    |
| 13             | 2        | 970    |
+----------------+----------+--------+
输出:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+

解决：

select customer_id, 
    count(customer_id) as count_no_trans 
    from visits where visit_id not in (select visit_id from transactions)
group by customer_id order by count_no_trans desc;

或者

select customer_id, 
    count(customer_id) as count_no_trans 
    from visits as v 
    left join transactions as t
    on (v.visit_id = t.visit_id) where t.amount is null
group by customer_id order by count_no_trans desc;

文章浏览I

知识点：order

Views 表：
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
此表无主键，因此可能会存在重复行。此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意，同一人的 author_id 和 viewer_id 是相同的。请编写一条 SQL 查询以找出所有浏览过自己文章的作者，结果按照 id 升序排列。

查询结果的格式如下所示：
Views 表：
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+
结果表：
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

select author_id as id
from Views
where author_id = viewer_id 
group by id order by id asc;

或者

select distinct author_id as id
from Views
where author_id = viewer_id 
order by id asc;

上升的温度

知识点：datediff，join

表： Weather
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id           | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是这个表的主键，该表包含特定日期的温度信息

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的id。返回结果不要求顺序。
示例 1：
输入：Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
输出：
+----+
| id |
+----+
| 2  |
| 4  |
+----+
解释：
2015-01-02 的温度比前一天高（10 -> 25），2015-01-04 的温度比前一天高（20 -> 30）

解决：

select w.id as 'Id'
from weather as w
join weather w2 on datediff(w.recordDate, w2.recordDate) = 1 and w.temperature > w2.temperature;

select w1.id as 'Id'
from weather w1, weather w2
where datediff(w1.recordDate, w2.recordDate) = 1 and w1.temperature > w2.temperature;

错误解法：

select w1.id as Id from Weather as w1
join Weather as w2
on (w1.recordDate = w2.recordDate + 1 or w1.recordDate = w2.recordDate -1) where (w1.Temperature > w2.Temperature);
-- 两个日期之间的时间差不能这样计算，要用时间函数datediff(w1.RecordDate,w2.RecordDate)，这样计算好像在跨年时也是错的

datediff(日期1, 日期2)：得到的结果是日期1与日期2相差的天数，
如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。

mysql> select datediff('2022-10-10','2022-10-11') as diffdate1,
    -> datediff('2022-10-11','2022-10-10') as diffdate2;
+-----------+-----------+
| diffdate1 | diffdate2 |
+-----------+-----------+
|        -1 |         1 |
+-----------+-----------+

另一个关于时间计算的函数是：timestampdiff(时间类型, 日期1, 日期2)，

这个函数和上面diffdate的正、负号规则刚好相反；日期1大于日期2，结果为负，日期1小于日期2，结果为正。

mysql> select timestampdiff(day,'2022-10-10 12:00:00','2022-10-11 18:30:00') as diffdate1,
    -> timestampdiff(day,'2022-10-10 16:30:00','2022-10-09 11:30:00') as diffdate2;
+-----------+-----------+
| diffdate1 | diffdate2 |
+-----------+-----------+
|         1 |        -1 |
+-----------+-----------+

mysql> select timestampdiff(hour,'2022-10-10 12:00:00','2022-10-10 18:30:00') as diffdate1,
    -> timestampdiff(hour,'2022-10-10 16:30:00','2022-10-10 11:30:00') as diffdate2;
+-----------+-----------+
| diffdate1 | diffdate2 |
+-----------+-----------+
|         6 |        -5 |
+-----------+-----------+

mysql> select timestampdiff(second,'2022-10-10 12:00:00','2022-10-10 12:00:30') as diffdate1,
    -> timestampdiff(second,'2022-10-10 12:00:30','2022-10-10 12:00:00') as diffdate2;
+-----------+-----------+
| diffdate1 | diffdate2 |
+-----------+-----------+
|        30 |       -30 |
+-----------+-----------+

销售员

知识点：多表，having, sum(if())

表: SalesPerson
+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| sales_id        | int     |
| name            | varchar |
| salary          | int     |
| commission_rate | int     |
| hire_date       | date    |
+-----------------+---------+
Sales_id是该表的主键列。该表的每一行都显示了销售人员的姓名和ID，以及他们的工资、佣金率和雇佣日期。

表: Company
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| com_id      | int     |
| name        | varchar |
| city        | varchar |
+-------------+---------+
Com_id是该表的主键列。该表的每一行都表示公司的名称和ID，以及公司所在的城市。

表: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  |
| order_date  | date |
| com_id      | int  |
| sales_id    | int  |
| amount      | int  |
+-------------+------+
Order_id是该表的主键列。com_id是Company表中com_id的外键。sales_id是来自销售员表com_id的外键。
该表的每一行包含一个订单的信息。这包括公司的ID、销售人员的ID、订单日期和支付的金额。

编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。以 任意顺序 返回结果表。
示例 1:
输入: SalesPerson 表:
+----------+------+--------+-----------------+------------+
| sales_id | name | salary | commission_rate | hire_date  |
+----------+------+--------+-----------------+------------+
| 1        | John | 100000 | 6               | 4/1/2006   |
| 2        | Amy  | 12000  | 5               | 5/1/2010   |
| 3        | Mark | 65000  | 12              | 12/25/2008 |
| 4        | Pam  | 25000  | 25              | 1/1/2005   |
| 5        | Alex | 5000   | 10              | 2/3/2007   |
+----------+------+--------+-----------------+------------+
Company 表:
+--------+--------+----------+
| com_id | name   | city     |
+--------+--------+----------+
| 1      | RED    | Boston   |
| 2      | ORANGE | New York |
| 3      | YELLOW | Boston   |
| 4      | GREEN  | Austin   |
+--------+--------+----------+
Orders 表:
+----------+------------+--------+----------+--------+
| order_id | order_date | com_id | sales_id | amount |
+----------+------------+--------+----------+--------+
| 1        | 1/1/2014   | 3      | 4        | 10000  |
| 2        | 2/1/2014   | 4      | 5        | 5000   |
| 3        | 3/1/2014   | 1      | 1        | 50000  |
| 4        | 4/1/2014   | 1      | 4        | 25000  |
+----------+------------+--------+----------+--------+
输出: 
+------+
| name |
+------+
| Amy  |
| Mark |
| Alex |
+------+
解释:
根据表 orders 中的订单 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。
所以我们需要输出表 salesperson 中所有其他人的名字。

解决：

select s.name from salesperson s
	left join orders o on (o.sales_id = s.sales_id)
	left join company c on (c.com_id = o.com_id)
group by s.name
having sum(if(c.name = 'RED', 1, 0)) = 0
order by s.sales_id;

或者

select s.name 
from salesperson s 
where s.sales_id not in
	(select o.sales_id from orders o
		left join company c on c.com_id = o.com_id where c.name = 'RED');

思路分解：

-- 如果我们知道向 `RED` 公司销售东西的人，那么我们要知道没有向 `RED` 公司销售东西的人会非常容易。 
-- 用一个临时表保存向 RED 公司销售过东西的人，然后利用姓名信息将这个表和 salesperson 表建立联系。
sql> select * from orders o
    -> left join company c on (c.com_id = o.com_id)
    -> where c.name = 'RED';
+----------+---------------------+--------+----------+--------+--------+------+--------+
| order_id | order_date          | com_id | sales_id | amount | com_id | name | city   |
+----------+---------------------+--------+----------+--------+--------+------+--------+
|        3 | 2014-01-03 00:00:00 |      1 |        1 |  50000 |      1 | RED  | Boston |
|        4 | 2014-01-04 00:00:00 |      1 |        4 |  25000 |      1 | RED  | Boston |
+----------+---------------------+--------+----------+--------+--------+------+--------+
-- 列 sales_id 在 salesperson 中，所以我们把它当做子查询并使用 NOT IN 获得想要的数据。