描述 177.第N高的薪水
表:
Employee
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ 在 SQL 中,id 是该表的主键。 该表的每一行都包含有关员工工资的信息。查询
Employee
表中第n
高的工资。如果没有第n
个最高工资,查询结果应该为null
。查询结果格式如下所示。
示例 1:
输入: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ n = 2 输出: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+示例 2:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ n = 2 输出: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | null | +------------------------+
数据准备
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')
分析
法一 可以使用limit 取第N个
limit 2,1 就是 第三高 那么 limit n-1,1 就是第N个
法二 用窗口函数给工资排名 取第N个
代码
法一:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare n int;
set n = N-1;
RETURN (
select salary from employee order by salary desc limit n,1);
END
法二:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (with t1 as (select salary,
dense_rank() over (order by salary desc) r1
from employee)
select distinct salary
from t1
where r1 = N);
END;