SQL求第top N
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+
思路:去重+row_number()编号+排序 + 取第N个
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select
case
when max_m >= 1 then Salary else null
end
as SecondHighestSalary
from
(
select
Salary,
max(mm) as max_m
from
(
select
Salary,
row_number() over(order by Salary desc) mm
from(
select distinct Salary from Employee
)t1
)t2
where mm=N
)t3
);
END