题目链接:https://leetcode-cn.com/problems/nth-highest-salary/
sql架构
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')
编写一个SQL查询来报告 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 |
+------------------------+
代码实现
#method1
#case1
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
RETURN (
select ifnull(
(
select distinct Salary
from Employee
order by Salary desc limit N,1
),null
) as getNthHighestSalary);
END
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#case2
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int;
set m = n-1;
RETURN (
select
(select distinct salary from employee
order by salary desc
limit 1 offset m) as getNthHighestSalary
);
END
# limit n,1 代表从第n条数据开始查询,1代表查询的条数
# limit 0,1 其实就代表查询第一条数据
# limit 1,1 其实就代表查询第二条数据
# limit 1 offset 1:limit后面的数字是查询几条数据,offset后面的数据是从哪条数据开始查询
# limit 2 offerset 3:也就是查询4和5这两条数据
#method2
#case1
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select (
select distinct salary from (
select salary, dense_rank() over (order by salary desc) rnk from employee
) t
where rnk=N
) getNthHighestSalary
);
END
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#case2
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
with t as (select salary,dense_rank() over (order by salary desc) as rank1
from Employee)
select distinct ifnull(t.salary,null)
from t
where t.rank1=N
);
END
#窗口函数dense_rank() over是连续排序存在2个并列第1的时候后面的名次显示2