Leetcode|MySQL|数据库刷题记录（175~597）

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+

首先set N = N-1, salary要distinct，降序排序，用limit 1 offset N取第N高的薪水，或者limit N,1

记得要加上ifnull判断是否存在记录，如果不存在要返回null

ps: limit接受一个或两个整型参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目，如limit 5,1就是偏移5行返回一行，即返回第6行；或者使用limit 1 offset 5也可以达到同样效果

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1;
    RETURN (
      # Write your MySQL query statement below.
      select
          ifnull(
            (select distinct Salary 
            from Employee
            order by Salary DESC
            limit 1 offset N),
            null
          )
  );
END

178.分数排名

https://leetcode-cn.com/problems/rank-scores/

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

（1）分析：首先恶补了一下窗口函数的用法https://blog.csdn.net/weixin_39010770/article/details/87862407

其中，序号函数包括ROW_NUMBER()，RANK()，DENSE_RANK()

ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

（2）解题思路：根据这道题的情况，应该使用DENSE_RANK()，并且使用ORDER BY字句对Score字段进行降序排序，之后DENSE_RANK()会将排序后的记录顺序进行编号。

# Write your MySQL query statement below
select Score,
DENSE_RANK() OVER (ORDER BY Score desc) as 'Rank'
from Scores;

180. 连续出现的数字

https://leetcode-cn.com/problems/consecutive-numbers/

表：Logs

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按任意顺序排列。

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

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。

思路：使用窗口函数 LEAD(expr,n)返回当前行的后n行的expr值。

具体来说，使用LEAD函数获取后1行和后2行的Num值，然后看当前行的Num和后1行、后2行的Num值相不相等，若相等则说明这个Num连续出现了3次以上。要注意一点的是要使用distinct对得到的ConsecutiveNums去重（有连续出现4次以上的情况）。

select distinct Num as ConsecutiveNums  
from (
    select Id, Num, LEAD(Num, 1) OVER () AS post_1, LEAD(Num, 2) OVER () AS post_2
    from Logs
) as t where Num=post_1 and post_1=post_2

181. 超过经理收入的员工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/

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 |
+----------+

思路：把Employee表复制一张，用第一张表的a.ManagerId和第二张表的b.Id连接两张表，然后判断两张表的Salary大小

SELECT a.Name AS Employee
FROM Employee AS a JOIN Employee AS b ON a.ManagerId=b.Id
WHERE a.Salary>b.Salary

182. 查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+
根据以上输入，你的查询应返回以下结果：

+---------+
| Email |
+---------+
| [email protected] |
+---------+
说明：所有电子邮箱都是小写字母。

思路：用GROUP BY对Email进行分组，然后用HAVING对分组后的Email进行COUNT统计，判断该email的出现次数是否大于1

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email)>1

183. 从不订购的客户

https://leetcode-cn.com/problems/customers-who-never-order/

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+

思路1：LEFT JOIN Customers表和Orders表，然后选择拼接后的表中Orders.Id is null的字段

SELECT a.Name as Customers
FROM Customers AS a LEFT JOIN Orders AS b ON a.Id=b.CustomerId
WHERE b.Id is null

思路2：使用NOT IN可以查找一张表的某个字段的值不在另一张表的某个字段的值

SELECT Name as Customers
FROM Customers 
WHERE Id NOT IN(
    SELECT CustomerId
    FROM Orders
)

184. 部门工资最高的员工

https://leetcode-cn.com/problems/department-highest-salary/

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

思路1：使用窗口函数RANK()先对Employee表根据DepartmentId进行分组，使用ORDER BY对窗口内字段根据Salary进行DESC排序，得到rank，将该表与Department表连接，取rank=1的行即为所求

SELECT b.Name as Department, a.Name AS Employee, a.Salary
FROM 
(SELECT *, RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS rk FROM Employee) AS a 
JOIN Department AS b 
ON a.DepartmentId=b.Id
WHERE a.rk=1

思路2：先拼接Employee表和Department表，然后找到Employee表中每个部门最高的薪水及对应的部门id，返回拼接的表中薪水为最高薪水且部门对应的行

SELECT b.Name as Department, a.Name AS Employee, a.Salary
FROM Employee AS a JOIN Department AS b ON a.DepartmentId=b.Id
WHERE (a.DepartmentId, a.Salary) IN
(
    SELECT DepartmentId, MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId
)

185. 部门工资前三高的所有员工

https://leetcode-cn.com/problems/department-top-three-salaries/

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

思路：和上一题的思路1是一致的，使用DENSE_RANK()先对Employee的每个部门按照Salary进行DESC排序，得到rank，将该表与Department表连接，取rank<=3的行即为所求

ps: 按照题目解释应该使用DENSE_RANK，DENSE_RANK是并列排序且不跳过序号，结果是1,1,2,3的形式，因此只要筛选rank<=3即可得到前三的工资

SELECT b.Name as Department, a.Name AS Employee, a.Salary
FROM 
(SELECT *, DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS rk FROM Employee) AS a 
JOIN Department AS b 
ON a.DepartmentId=b.Id
WHERE a.rk<=3

196. 删除重复的电子邮箱

https://leetcode-cn.com/problems/delete-duplicate-emails/

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

+----+------------------+
| Id | Email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email &n