177.第N高的薪水
编写一个 SQL 查询语句,获取Employee
表中第n高的薪水(Salary)。
用到的表和数据SQL:
Create table If Not Exists Employee (Idint, 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');
答案:
此题和第二高的薪水有相似之处,算作它的扩展题,这里也可以作为第二高薪水的答案;
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select Max(Salary) from Employee e1
where N = ( select count(distinct(e2.Salary)) from Employee e2 where e2.Salary >=e1.Salary )
);
END
写的比较粗糙,有不理解的可以扫描二维码加QQ群找我解答。