177. Nth Highest Salary
给定一个Employee表,要找出其中第N高的薪资(Salary)
解法一:
从Employee中,找出前N-1高的Salary,在剩下的薪水中找出最高的那一个即可
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select max(Salary) as getNthHighestSalary
from Employee e1
where N - 1 =
(select count(Distinct Salary) from Employee e2
where e1.Salary < e2.Salary)
);
END
解法二:
运用limit
,limit m, n
表示,从表格的第m + 1行开始,选取N个数(SQL从0开始计数,因此m对应第m+1行,或者跳过m行)
上式也相当于limit n offset m
,即跳过m行,取n个数
因此先将Salary按降序排序,然后跳过N-1个数,取接下来的一个数即可(注意Distinct
关键词)
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
# Write your MySQL query statement below.
select distinct(Salary) as getNthHighestSalary
from Employee
GROUP BY Salary ORDER BY Salary DESC limit 1 offset N
);
END