[leetcode] 177.nth highest salary 第n高薪水
write a sql query to get the nth highest salary from the employee table.
+—-+——–+
| id | salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+
for example, given the above employee table, the nth highest salary where n = 2 is 200. if there is no nth highest salary, then the query should return null.
这道题是之前那道second highest salary的拓展,根据之前那道题的做法,我们可以很容易的将其推展为n,根据对second highest salary中解法一的分析,我们只需要将offset后面的1改为n-1就行了,但是这样mysql会报错,估计不支持运算,那么我们可以在前面加一个set n = n – 1,将n先变成n-1再做也是一样的:
解法一:
create function getnthhighestsalary(n int) returns int begin set n = n - 1; return ( select distinct salary from employee group by salary order by salary desc limit 1 offset n ); end
根据对second highest salary中解法四的分析,我们只需要将其1改为n-1即可,这里却支持n-1的计算,参见代码如下:
解法二:
create function getnthhighestsalary(n int) returns int begin return ( select max(salary) from employee e1 where n - 1 = (select count(distinct(e2.salary)) from employee e2 where e2.salary > e1.salary) ); end
当然我们也可以通过将最后的>改为>=,这样我们就可以将n-1换成n了:
解法三:
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
类似题目:
second highest salary
参考资料:
到此这篇关于sql实现leetcode(177.第n高薪水)的文章就介绍到这了,更多相关sql实现第n高薪水内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!