leetcode177 第N高的薪水 Nth Highest Salary
编写一个 SQL 查询语句,获取Employee
表中第n高的薪水(Salary)。
创建表和数据
Create table If Not Exists Employee (Id int, 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.对每一个薪水A,只要大于等于A的不同薪水个数等于N即可。
因此子查询求出大于等于A的不同薪水个数B。当B=A时,能得出结果。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( select distinct e1.Salary from Employee e1 where N = ( select count(distinct e2.Salary) from Employee e2 where e2.Salary >= e1.Salary ) ); END
2.直接用order by和limit。要注意的是,limit的偏移从0开始。第N个变成参数时,要改为N-1。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN declare p int default N - 1; RETURN ( select distinct salary from Employee order by salary desc limit p,1 ); END