177. Nth Highest Salary

Question

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                    |
+------------------------+

Thinking:

• Method:
  • 主要还是要研究create function的使用方法。
    • 声明变量使用declare
    • 设置变量值 set

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
            M, 1
  );
END

二刷

1. offset关键字后的数据不能进行数学操作。这才是我们需要在外部对N - 1进行赋值的原因。

   CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
   BEGIN
   DECLARE M int;
   SET M = N - 1;
     RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL(
          (SELECT DISTINCT Salary
          FROM Employee
          ORDER BY Salary DESC LIMIT 1 OFFSET M), NULL) as Salary
     );
   END