【leetcode两题选手】MySQL类题目（二）

题一：第N高的薪水

可以说是对上一题的巩固拓展吧。

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

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

• 1
• 2
• 3
• 4
• 5
• 6
• 7

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

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

• 1
• 2
• 3
• 4
• 5

来源：力扣（LeetCode） 链接：https://leetcode-cn.com/problems/nth-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解

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

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

收获

怎么说呢，昨天刷了一道这样的题，没记太清楚，今天再做一题，把格式记住了。
leetcode两题选手 - MySQL类题目（一）

这里不多做赘述。

题二：分数排名

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

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

+----+-------+
| Id | Score |
+----+-------+
|


    
 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank

来源：力扣（LeetCode） 链接：https://leetcode-cn.com/problems/rank-scores
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解

select score, 
       dense_rank() over(order by Score desc) as Ranking
from Scores;

• 1
• 2
• 3

我就想说这不是为难我这个新手吗？这是要保留两位数？这也没说啊。。。

解题思路：

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号
同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3
不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。

值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。

row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

• 1
• 2
• 3
• 4

显然，本题是要用第三个函数。
另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据

• 1
• 2

收获

涉及到排名的问题，都可以使用窗口函数来解决。记住rank, dense_rank, row_number排名的区别。
MySQL窗口函数