题目描述:
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
# 方法一:这种方法不能处理返回记录为null的情况
#select Salary as 'SecondHighestSalary' from Employee order by Salary desc limit 1 offset 1
# 方法二:
select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset 1), null) as SecondHighestSalary
# 方法三:
#select max(Salary) as 'SecondHighestSalary' from Employee where Salary < (select max(Salary) from Employee)
第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n = N - 1;
RETURN (
# Write your MySQL query statement below.
select distinct Salary from Employee order by Salary desc limit n,1
);
END