题目:编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
解题思路1:单表查询
由于本题不存在分组排序,只需返回全局第N高的一个,所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节:
(1)同薪同名且不跳级的问题,解决办法是用group by按薪水分组后再order by
(2)排名第N高意味着要跳过N-1个薪水,由于无法直接用limit N-1,所以需先在函数开头处理N为N=N-1。
注:这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数(意味着0、负数、小数都不行)或者单一变量(意味着不能用表达式),也就是说想取一条,limit 2-1、limit 1.1这类的写法都是报错的。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
注:这种解法形式最为简洁直观,但仅适用于查询全局排名问题,如果要求各分组的每个第N名,则该方法不适用;而且也不能处理存在重复值的情况。
解题思路2:窗口函数
这个问题可以考虑窗口函数,先了解一些几个排序函数:
(1)row_number(): 同薪不同名,相当于行号,例如3000、2000、2000、1000排名后为1、2、3、4
(2)rank(): 同薪同名,有跳级,例如3000、2000、2000、1000排名后为1、2、2、4
(3)dense_rank(): 同薪同名,无跳级,例如3000、2000、2000、1000排名后为1、2、2、3
(4)ntile(): 分桶排名,即首先按桶的个数分出第一二三桶,然后各桶内从1排名,实际不是很常用
显然,本题是要用第三个函数。
另外这三个函数必须要要与其搭档over()配套使用,over()中的参数常见的有两个,分别是
partition by,按某字段切分
order by,与常规order by用法一致,也区分ASC(默认)和DESC,因为排名总得有个依据
注:下面代码仅在mysql8.0以上版本可用
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END