第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 FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N -1;
RETURN (
select distinct salary
from Employee
order by salary desc
limit N, 1
);
END
方法二 子查询
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare x int;
set x = N - 1;
RETURN (
# Write your MySQL query statement below.
select
IFNULL(
(select distinct salary from Employee
order by salary desc
limit x, 1
),null
)
);
END