Write a SQL query to get the second highest salary from the Employee
table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
思路:熟悉limit 用法;求offset 1之后的row count1,就是第二大,求offset n - 1之后的第一个就是第N大;
# Write your MySQL query statement below
select (select distinct Salary from Employee order by Salary desc limit 1, 1) as SecondHighestSalary
求第N大;
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N - 1;
RETURN (
select distinct Salary from Employee Order by Salary desc limit M, 1
);
END