[LeetCode] Second Highest Salary

中文题目

写一个 SQL 查询语句，获取 Employee 表中第二高的Salary 。

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如, 上面给出的 Employee 表，查询应该返回 200 作为第二高的Salary。如果没有第二高的Salary，那么查询应该返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

英文题目

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 |
+---------------------+

参考答案

方法一：

SELECT Salary FROM Employee GROUP BY Salary
UNION ALL (SELECT NULL AS Salary)
ORDER BY Salary DESC LIMIT 1 OFFSET 1;

方法二：

SELECT MAX(Salary) FROM Employee 
WHERE Salary NOT IN
(SELECT MAX(Salary) FROM Employee);

方法三：

SELECT MAX(Salary) FROM Employee
Where Salary <
(SELECT MAX(Salary) FROM Employee);

方法四：

SELECT MAX(Salary) FROM Employee E1
WHERE 1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary);