LeetCode Database Medium & Hard

前言

实验环境

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

MYSQL Tips

• CRTL+L：清屏

常用网址

• MySQL Tutorial
• Stack Overflow
• Database Administrators Stack Exchange

177. Nth Highest Salary

分类：数据库
难度：中等

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

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 暂不支持。

SELECT Score, DENSE_RANK()
    OVER (ORDER BY Score DESC)
    AS 'Rank'
FROM Scores
ORDER BY Score DESC;

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

SELECT Score,(
    -- 去重计算 Rank
    SELECT COUNT(DISTINCT Score)
    FROM Scores s2
    WHERE s1.Score<=s2.Score)
    AS 'Rank'
FROM Scores s1
ORDER BY Score DESC;

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

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 连续。

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 的数字去重输出。

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 中，然后将三个表进行连接，输出每个部门拥有最高薪水的职工信息。

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;

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

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 是连续自增的，因此不用考虑不连续的情况。

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 。

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 解题方法，先算出每个部门的工资排名，然后再把排名前三的员工信息输出。

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，相比之下执行速度较慢。

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 进行两次表连接，一次连接用户，一次连接司机，同时满足 Banned 为 No；然后筛选日期并根据日期分组；最后根据分组情况计算输出，保留两位小数。

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 s3 和 s1 s3 s2 返回的结果是相同的，只是顺序不同，因为这三天都满足条件，因此 6 种情况只要列出三种。

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;