LeetCode--177.第N高的薪水

建表

Create table If Not Exists Employee (Idint, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values('1', '100');
insert into Employee (Id, Salary) values('2', '200');
insert into Employee (Id, Salary) values('3', '300');

1.通过函数实现

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
select Max(Salary) from Employee e1 
where N = ( select count(distinct(e2.Salary)) from Employee e2 where e2.Salary >=e1.Salary )
 );
END

2.直接开窗函数取top n

select * from (
 select Id, Salary, row_number() over(ORDER BY Salary desc) ranks from Employee111 
) t where t.ranks=2