当前位置: 萬仟网 > IT编程>数据库>Mysql > LeetCode——Nth Highest Salary

LeetCode——Nth Highest Salary

2019年10月09日 17:51  | 萬仟网IT编程  | 我要评论
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.

+------------------------+
| getnthhighestsalary(2) |
+------------------------+
| 200                    |
+------------------------+

此题相较于second highest salary做了一些改进:

  • 创建mysql function;
  • 需要判断传入参数的合理性.

因此,对代码改动如下所示:

create function getnthhighestsalary(n int) returns int
begin
  declare p int default n-1;
  if (p<0) then
    return null;
  else
  return (
      # write your mysql query statement below.
      select ifnull(
            (
                select distinct salary 
                from employee 
                order by salary desc 
                limit p,1)
            ,null)
          as secondhighestsalary   
  );
  end if;
end

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com