1 获取第二大
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
先获取最大,然后剔除第大,剩下的最大就是就是第二大
select
max(Salary)
from
Employee
where
Salary < (select max(Salary) from Employee)
2 获取第N高
思路:获取limit N 来获取前n个,然后取最小
从中间表select时,中间表要命名别名t
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select case when count(1)<N then null else min(t.Salary) end
from
(select Salary from Employee order by Salary desc limit N) t
);
END