Description
Write a SQL query to get the nth highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
其实 Leetcode 上还有一道选第第二大元素的题目，不过和这道题实质上一毛一样，所以就只记录这一个题目了。
选第N大的元素思路已经很明朗了，由于 sql 内置的函数不存在选第N大元素的函数，所以我们只能另辟蹊径了，就根据Salary排序排序，然后用offset选排序后的第N个元素，最后用LIMIT限制选择一个元素，就是第N大的那个元素了。
本来我的解法是这样的：
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC LIMIT 1 OFFSET M
);
END
但是这样出来有一个问题，当第N大不存在时，返回的结果是个空集。而题目一般期望当没有值时，返回一个null,于是需要套个 SELECT ([expression]) AS Salary 这样的壳，所以最后结果是这样的：
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT (
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC LIMIT 1 OFFSET M
)
AS getNthHighestSalary
);
END
