前言

实验环境

  • 英文题面
  • docker-machine with virtualbox drive
    • mysql 8.0.19

MYSQL Tips

  • CRTL+L:清屏

常用网址

177. Nth Highest Salary

分类:数据库
难度:中等

学数据库的时候貌似有写过函数(忘得差不多了),注意点是无法直接使用参数 N,还有需要使用 DISTINCT 去重。

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT N, 1
);
END

178. Rank Scores

分类:数据库
难度:中等

针对样例,使用 RANK() 函数输出的 Rank 顺序是:1、1、3、4、4、6,不满足要求,因此得用 DENSE_RANK() 函数。

以下语句在本地 mysql 8.0.19 环境下可以运行,LeetCode 暂不支持。

1
2
3
4
5
SELECT Score, DENSE_RANK()
OVER (ORDER BY Score DESC)
AS 'Rank'
FROM Scores
ORDER BY Score DESC;

为了过题就得换一种写法了x

1
2
3
4
5
6
7
8
SELECT Score,(
-- 去重计算 Rank
SELECT COUNT(DISTINCT Score)
FROM Scores s2
WHERE s1.Score<=s2.Score)
AS 'Rank'
FROM Scores s1
ORDER BY Score DESC;

评论区翻到的另一种写法,相比之下执行速度快多了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT Score,
CASE
-- 比较,排名不变
WHEN @pre = Score THEN @rk + 0
-- 赋值,排名增加(CASE 语句第一次执行这句,返回 THEN 之后的 @rk,即 1)
WHEN @pre := Score THEN @rk := @rk + 1
-- 排名增加
ELSE @rk := @rk + 1 END
AS 'Rank'
FROM Scores, (
-- 变量初始值
SELECT @pre := NULL, @rk := 0
)AS tmp
ORDER BY Score DESC;

180. Consecutive Numbers

分类:数据库
难度:中等

不知道为什么想到了三联书店23333,这种写法要求 Id 连续。

1
2
3
4
5
6
7
SELECT DISTINCT L1.Num
AS ConsecutiveNums
FROM Logs L1, Logs L2, Logs L3
WHERE L2.Id = L1.Id + 1
AND L2.Num = L1.Num
AND L3.Id = L1.Id + 2
AND L3.Num = L1.Num;

仿照上题的写法,首先生成 res 临时表,统计数字和连续出现的次数,然后将连续出现次数大于等于 3 的数字去重输出。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT Num
AS ConsecutiveNums
FROM (
SELECT Num,
CASE
-- 比较
WHEN @pre = Num THEN @cnt := @cnt + 1
-- 赋值
WHEN @pre := Num THEN @cnt := 1
END
AS Cnt
FROM Logs, (
SELECT @pre := NULL, @cnt := 0) AS tmp
)AS res
WHERE res.Cnt >= 3;

184. Department Highest Salary

分类:数据库
难度:中等

先统计得出每个部门的最高薪水,将数据存储在临时表 tmp 中,然后将三个表进行连接,输出每个部门拥有最高薪水的职工信息。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT D.Name AS Department,
E.Name AS Employee,
Salary
FROM Employee AS E,
Department AS D, (
SELECT DepartmentId, MAX(Salary) AS Salary
FROM Employee
GROUP BY DepartmentId
)AS tmp
WHERE E.DepartmentId = D.Id
AND D.Id = tmp.DepartmentId
AND E.Salary = tmp.Salary;

官方题解是同样的思路,不过看起来可读性更强一些。

1
2
3
4
5
6
7
8
9
10
SELECT D.name AS Department,
E.Name AS Employee,
Salary
FROM Employee AS E
JOIN Department AS D
ON E.DepartmentId = D.Id
WHERE (E.DepartmentId, Salary) IN(
SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId);

626. Exchange Seats

分类:数据库
难度:中等

题目表示 id 是连续自增的,因此不用考虑不连续的情况。

1
2
3
4
5
6
7
SELECT (
IF(s.id%2=0, s.id-1,
IF(s.id=(SELECT MAX(id) FROM seat), s.id, s.id+1)
)
) AS id, student
FROM seat s
ORDER BY id;

学习一下官方题解使用 COALESCE() 函数,顺便根据题解里给出的函数说明链接可以推测 LeetCode 上使用的是 mysql 5.7 。

1
2
3
4
5
SELECT s1.id, COALESCE(s2.student, s1.student) AS student
FROM seat s1
LEFT JOIN seat s2
ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;

185. Department Top Three Salaries

分类:数据库
难度:困难

根据 178 解题方法,先算出每个部门的工资排名,然后再把排名前三的员工信息输出。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT D.Name AS Department,
E.Name AS Employee,
E.Salary
FROM Department AS D,(
SELECT DepartmentId, Name, Salary,(
SELECT COUNT(DISTINCT Salary)
FROM Employee E2
WHERE E1.Salary <= E2.Salary
AND E1.DepartmentId = E2.DepartmentId
)AS 'Rank'
FROM Employee E1
)AS E
WHERE D.Id = E.DepartmentId
AND E.Rank <= 3;

翻出官方题解,思路是判断比当前工资高的(去重)小于 3,相比之下执行速度较慢。

1
2
3
4
5
6
7
8
9
10
11
SELECT D.Name AS 'Department',
E1.Name AS 'Employee',
E1.Salary
FROM Employee E1
JOIN Department D
ON E1.DepartmentId = D.Id
WHERE 3 > (
SELECT COUNT(DISTINCT E2.Salary)
FROM Employee E2
WHERE E2.Salary > E1.Salary
AND E1.DepartmentId = E2.DepartmentId);

262. Trips and Users

分类:数据库
难度:困难

先根据 Id 进行两次表连接,一次连接用户,一次连接司机,同时满足 BannedNo;然后筛选日期并根据日期分组;最后根据分组情况计算输出,保留两位小数。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT T.Request_at AS Day, 
ROUND(SUM(IF(T.Status LIKE '%cancel%', 1, 0))/COUNT(T.Status), 2)
AS 'Cancellation Rate'
FROM Trips AS T
JOIN Users AS U1
ON (T.Client_Id = U1.Users_Id
AND U1.Banned = 'No')
JOIN Users AS U2
ON (T.Driver_Id = U2.Users_Id
AND U2.Banned = 'No')
WHERE T.Request_at BETWEEN '2013-10-01'AND '2013-10-03'
GROUP BY Request_at;

601. Human Traffic of Stadium

分类:数据库
难度:困难

题目的意思不是连续三天,特指三条连续记录,所以得用 id 条件才能过(摊手),根据 180 的解题思路拓展,考虑到所有情况即可。

s1 s2 s3s1 s3 s2 返回的结果是相同的,只是顺序不同,因为这三天都满足条件,因此 6 种情况只要列出三种。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT s1.id, s1.visit_date, s1.people
FROM stadium s1, stadium s2, stadium s3
WHERE s1.people >= 100
AND s2.people >= 100
AND s3.people >= 100
AND (
-- s1 s2 s3
(s1.id = s2.id - 1 AND s1.id = s3.id - 2) OR
-- s3 s1 s2
(s1.id = s2.id - 1 AND s1.id = s3.id + 1) OR
-- s2 s3 s1
(s1.id = s2.id + 2 AND s1.id = s3.id + 1)
)
ORDER BY s1.id;