第N高的薪水

此题来自leetcode 177.

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）

Id	Salary
1	100
2	200
3	300

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null

getNthHighestSalary(2)
200

个人认为在成绩排名等问题中可以先考虑一下Mysql的窗口函数. 

方法1(子查询):

# 子查询解法
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
    # Write your MySQL query statement below.

    select distinct a.salary as getNthHighestSalary from employee a 
    where (
        select count(distinct salary) from employee where salary>a.salary 
        ) = N-1

  );
END

方法2:

# := 是赋值的作用,记住在赋值的时候使用:=就好了
# = 是相等意思  比如num = num+1 那永远返回0.  num=num 返回1

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N := N-1;   
  RETURN (
    SELECT DISTINCT
        salary AS getNthHighestSalary 
    FROM
	    employee 
    ORDER BY
	    salary DESC 
	    LIMIT N,1
  );
END

方法3(窗口函数):

# 解法用了窗口函数 dense_rank()

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
 
  RETURN (
    SELECT DISTINCT
	    salary 
    FROM
	    ( SELECT salary, dense_rank() over ( ORDER BY salary DESC ) AS rnk FROM employee ) tmp 
    WHERE
	    rnk = N
  );

END