目录
题目如下:
如果使用sql:select distinct salary as SecondHighestSalary from Employee order by salary desc limit 1 offset 1,这条语句确实可以满足要求,但是如果没有第二高薪水的,只有一条数据的话就会导致语句出错。
解决方法1
通过一个临时表来缓冲:select (select distinct salary from Employee order by salary desc limit 1 offset 1) as SecondHighestSalary
解决方法2
使用ifnull判断:select ifnull(select distinct salary from Employee oeder by salary desc limit 1 offset 1,null) as SecondHighestSalary
举一反三
题目如下:
求第N高薪水需要用函数去传个参数接收需要的N的数:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
# 从N的前一个开始
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
# limit 1 offset N 限制1条,从N开始
select ( select distinct salary from Employee order by salary desc limit 1 offset N ) as getNthHighestSalary
);
END