我要评论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
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论