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