LeetCode高频 SQL 50 题（基础版）—— 子查询(上级经理已离职的公司员工、换座位、电影评分、餐馆营业额变化增长) + 高级字符串函数 / 正则表达式 / 子句(修复表中的名字)

本文链接：https://blog.youkuaiyun.com/weixin_51968130/article/details/148240781

子查询

1978. 上级经理已离职的公司员工简单

表: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+
在 SQL 中，employee_id 是这个表的主键。
这个表包含了员工，他们的薪水和上级经理的id。
有一些员工没有上级经理（其 manager_id 是空值）。

查找这些员工的id，他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的manager_id 这一列还是设置的离职经理的id 。

返回的结果按照employee_id 从小到大排序。

查询结果如下所示：

示例：

输入：
Employees table:
+-------------+-----------+------------+--------+
| employee_id | name      | manager_id | salary |
+-------------+-----------+------------+--------+
| 3           | Mila      | 9          | 60301  |
| 12          | Antonella | null       | 31000  |
| 13          | Emery     | null       | 67084  |
| 1           | Kalel     | 11         | 21241  |
| 9           | Mikaela   | null       | 50937  |
| 11          | Joziah    | 6          | 28485  |
+-------------+-----------+------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 11          |
+-------------+

解释：
薪水少于 30000 美元的员工有 1 号(Kalel) 和 11号 (Joziah)。
Kalel 的上级经理是 11 号员工，他还在公司上班(他是 Joziah )。
Joziah 的上级经理是 6 号员工，他已经离职，因为员工表里面已经没有 6 号员工的信息了，它被删除了。

方法一：左连接
- 左连接自己：薪水严格小于30000对应e1.salary < 30000；
- 经理离职对应着有上级经理e1.manager_id is not null，和经理已离职e2.employee_id is null两个条件。
方法二：子查询 + not in
- 如果manager_id为null，则not in和in都不符合条件，所以，经理已离职可以直接用not in直接筛选。

/* Write your T-SQL query statement below */
--方法一：左连接
select e1.employee_id 
from Employees e1
left join Employees e2
on e1.manager_id = e2.employee_id
where e1.salary < 30000 and e1.manager_id is not null and e2.employee_id is null
order by e1.employee_id 

--方法二：not in + 子查询
select employee_id 
from Employees
where salary < 30000 
    and manager_id not in (select employee_id from Employees)
order by employee_id

626. 换座位中等

表: Seat

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id 是该表的主键（唯一值）列。
该表的每一行都表示学生的姓名和 ID。
ID 序列始终从 1 开始并连续增加。

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Seat 表:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
输出: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
解释:
请注意，如果学生人数为奇数，则不需要更换最后一名学生的座位。

select 
    if(id%2=0,
        id-1,
        if(id=(select count(distinct id) from seat),
            id,
            id+1)) 
    as id,student 
from seat 
order by id;

若id是偶数，减1
若id是奇数，加1
当总数为奇数时，最后一个id应保持不变，加1会导致空出一位。

if语法：IF(condition, value_if_true, value_if_false)

1341. 电影评分中等

表：Movies

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| title         | varchar |
+---------------+---------+
movie_id 是这个表的主键(具有唯一值的列)。
title 是电影的名字。

表：Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
+---------------+---------+
user_id 是表的主键(具有唯一值的列)。
'name' 列具有唯一值。

表：MovieRating

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
+---------------+---------+
(movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。
这个表包含用户在其评论中对电影的评分 rating 。
created_at 是用户的点评日期。

请你编写一个解决方案：

查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

返回结果格式如下例所示。

示例 1：

输入：
Movies 表：
+-------------+--------------+
| movie_id    |  title       |
+-------------+--------------+
| 1           | Avengers     |
| 2           | Frozen 2     |
| 3           | Joker        |
+-------------+--------------+
Users 表：
+-------------+--------------+
| user_id     |  name        |
+-------------+--------------+
| 1           | Daniel       |
| 2           | Monica       |
| 3           | Maria        |
| 4           | James        |
+-------------+--------------+
MovieRating 表：
+-------------+--------------+--------------+-------------+
| movie_id    | user_id      | rating       | created_at  |
+-------------+--------------+--------------+-------------+
| 1           | 1            | 3            | 2020-01-12  |
| 1           | 2            | 4            | 2020-02-11  |
| 1           | 3            | 2            | 2020-02-12  |
| 1           | 4            | 1            | 2020-01-01  |
| 2           | 1            | 5            | 2020-02-17  | 
| 2           | 2            | 2            | 2020-02-01  | 
| 2           | 3            | 2            | 2020-03-01  |
| 3           | 1            | 3            | 2020-02-22  | 
| 3           | 2            | 4            | 2020-02-25  | 
+-------------+--------------+--------------+-------------+
输出：
Result 表：
+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+
解释：
Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。
Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。

# Write your MySQL query statement below
(SELECT u.name AS results
FROM Users u
LEFT JOIN MovieRating mr ON u.user_id = mr.user_id
GROUP BY u.user_id
ORDER BY COUNT(*) DESC, name ASC
LIMIT 1)

UNION ALL

(SELECT title AS results
FROM Movies m
LEFT JOIN MovieRating mr ON m.movie_id = mr.movie_id AND YEAR(mr.created_at) = 2020 AND MONTH(mr.created_at) = 2
GROUP BY mr.movie_id
ORDER BY AVG(mr.rating) DESC, title
LIMIT 1);

思路：先写出两个子问题的SQL，再将结果UNION ALL
解题方法
- 评论最多的用户：User表左连接Mobies_Rating表，条件是user_id相等，根据u.user_id分块，求每块的记录数，按记录数倒序排序，并按名字升序排序，只取一条记录；查询结果命名为results
- 2月均分数最高的电影：Movies表左连接Mobies_Rating表，条件是movie_id相等并且mr.created_date必须在2020年2月，根据m.movie_id分块，求每块rating的平均数，按平均数倒序排序，并按名字升序排序，只取一条记录；查询结果命名为results
- 将两条结果合并，UNION ALL

YEAR(date) 函数

功能：从日期或日期时间值中提取年份部分（4 位数）

MONTH(date) 函数

功能：从日期或日期时间值中提取月份部分（1-12 的整数）。

1321. 餐馆营业额变化增长中等

表: Customer

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
在 SQL 中，(customer_id, visited_on) 是该表的主键。
该表包含一家餐馆的顾客交易数据。
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。
amount 是一个顾客某一天的消费总额。

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

返回结果格式的例子如下。

示例 1:

输入：
Customer 表:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         | 
| 6           | Elvis        | 2019-01-06   | 140         | 
| 7           | Anna         | 2019-01-07   | 150         |
| 8           | Maria        | 2019-01-08   | 80          |
| 9           | Jaze         | 2019-01-09   | 110         | 
| 1           | Jhon         | 2019-01-10   | 130         | 
| 3           | Jade         | 2019-01-10   | 150         | 
+-------------+--------------+--------------+-------------+
输出：
+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120            |
| 2019-01-09   | 840          | 120            |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+
解释：
第一个七天消费平均值从 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

首先找出一共有哪些日期有顾客访问：

SELECT DISTINCT visited_on FROM customer 作为表a，该表中从比最小的日期大6天的时间开始，就是最终结果所需要的日期（因为每天至少有一位顾客，所以一定连续）。

SELECT
    *
FROM
    ( SELECT DISTINCT visited_on FROM customer ) a
WHERE
    a.visited_on >= (SELECT min( visited_on ) FROM customer ) + 6

其次将该表a，与customer表b全连接，连接条件为a表的visited_on比b表大不超过6天，即可以作为输出结果的这些天，一周内涉及到了哪些交易记录。

SELECT
    *
FROM
    ( SELECT DISTINCT visited_on FROM customer ) a JOIN customer b 
    ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6 
WHERE
    a.visited_on >= (SELECT min( visited_on ) FROM customer ) + 6

根据表a的这些日期分组，对其连接到的符合条件的表b的金额进行聚合计算，并根据表a日期升序排列（默认ASC）

SELECT
    a.visited_on,
    sum( b.amount ) AS amount,
    round(sum( b.amount ) / 7, 2 ) AS average_amount 
FROM
    ( SELECT DISTINCT visited_on FROM customer ) a JOIN customer b 
    ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6 
WHERE
    a.visited_on >= (SELECT min( visited_on ) FROM customer ) + 6 
GROUP BY
    a.visited_on
ORDER BY
  a.visited_on

高级字符串函数 / 正则表达式 / 子句

1667. 修复表中的名字简单

表： Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

返回结果格式示例如下。

示例 1：

输入：
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

CONCAT() 函数
- CONCAT 可以将多个字符串拼接在一起。

LEFT(str, length) 函数
- 从左开始截取字符串，length 是截取的长度。
UPPER(str) 与 LOWER(str)
- UPPER(str) 将字符串中所有字符转为大写
- LOWER(str) 将字符串中所有字符转为小写

SUBSTRING(str, begin, end)
- m截取字符串，end 不写默认为空。
- SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

LeetCode高频 SQL 50 题（基础版）—— 子查询(上级经理已离职的公司员工、 换座位、电影评分、 餐馆营业额变化增长) + 高级字符串函数 / 正则表达式 / 子句(修复表中的名字)

子查询

1978. 上级经理已离职的公司员工 简单

626. 换座位 中等

1341. 电影评分 中等

1321. 餐馆营业额变化增长 中等

高级字符串函数 / 正则表达式 / 子句

1667. 修复表中的名字 简单

LeetCode高频 SQL 50 题（基础版）—— 子查询(上级经理已离职的公司员工、换座位、电影评分、餐馆营业额变化增长) + 高级字符串函数 / 正则表达式 / 子句(修复表中的名字)

1978. 上级经理已离职的公司员工简单

626. 换座位中等

1341. 电影评分中等

1321. 餐馆营业额变化增长中等

1667. 修复表中的名字简单