sql第N工资问题: leetcode 177. Nth Highest Salary

leetcode链接：https://leetcode.com/problems/nth-highest-salary/

---

mysql解法：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
     select MIN(t3.salary) from (
      select case when @salary = salary then   @rank else @rank := @rank+1 end   as rank ,@salary :=  t1.salary AS salary  
    from  ((
      select t.id,t.salary  
          from employee t
        order by t.salary desc
  ) t1,(select @rank := 0) t2,(select @salary := 0) t4)
       
      ) t3 
      where  t3.rank = n
  );
END

---

oracle解法:

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    with p as (
    select t.id,t.salary ,dense_rank()over(order by t.salary desc) rn
        from employee t
 )
    select max(t1.salary) 
    into result
    from p t1
    where t1.rn = n;
    RETURN result;
    
END;

---

重点：sql的连续排序实现