- 先查詢出以
Salary
降序排序,並去重後的前N行記錄select distinct Salary from Employee order by Salary desc limit N
- 再從該結果集中,找到最小的那一行記錄
select Salary as getNthHighestSalary from ( select distinct Salary from Employee order by Salary desc limit N ) b order by Salary asc limit 1
- 由於題目限制,
如果不存在第 n 高的薪水,那麼查詢應返回 null
,那麼結果還應該判斷N
是否大於去重後的記錄總數select if( ( select count(distinct Salary) from Employee ) < N, null, ( select Salary as getNthHighestSalary from ( select distinct Salary from Employee order by Salary desc limit N ) b order by Salary asc limit 1 ) )
所以最終答案為
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select if(
(
select count(distinct Salary)
from Employee
) < N,
null,
(
select Salary as getNthHighestSalary
from (
select distinct Salary
from Employee
order by Salary desc
limit N
) b
order by Salary asc
limit 1
)
)
);
END