19 第N高的薪水
Opened this issue · 0 comments
astak16 commented
题目
编写一个 SQL 查询,获取 employee
表中第 N 高的薪水(salary)。
create table employee (
id int primary key auto_increment,
salary int
);
insert into employee (salary) values(100),(200),(300);
SQL:方法一
create function getNthHighestSalary(N int) returns int
begin
return(
select e.salary from (
select salary,
case when id, salary is not null then @rank:=@rank + 1 end as 排名
from (select @rank:=0) as init, (
select salary from employee
group by id, salary
order by salary desc
) as t
) as e where e.排名 = N
)
end
解析
外面加了一个函数,把 where
条件变成变量。
SQL:方法二
create function getNthHighestSalary(N int) returns int
declare m int;
set m = N - 1;
begin
return(
select salary from employee group by salary order by salary desc limit m, 1
);
end
解析
limit
这里没有用变量时,是从0
开始的,这里的N
是从1
开始的,所里这里要减1
。- 使用
declare
声明变量m
- 使用
set
对变量m
进行赋值
SQL:方法三
create function getNthHighestSalary(N int) returns int
begin
return (
select salary from (
select id, salary,
@rank:= if(salary is not null, @rank + 1, @rank) as 排名
from employee, (select @rank:=0) as init
group by id, salary
order by salary desc
) as e where 排名 = N
);
end
解析
和方法一一样,把 case
换成了 if