[LeetCode][SQL]Second Highest Salary

Second Highest Salary 

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 second highest salary is 200. If there is no second highest salary, then the query should return null.

https://leetcode.com/problems/second-highest-salary/

 

 

---

 

 

首先MySQL不支持top语法，要用limit代替。

select Salary from Employee order by Salary desc limit 1,1

这样写的话如果不存在，select出来是空，题目需要是null(掀桌。

Input:    {"headers": {"Employee": ["Id", "Salary"]}, "rows": {"Employee": [[1, 100]]}}
Output:    {"headers": ["Salary"], "values": []}
Expected:    {"headers": ["SecondHighestSalary"], "values": [[null]]}

 

于是就需要在外面再包一层。为什么要加distinct呢，是因为不加过不了这个case：

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 100    | 
+----+--------+

最终解法:

select (select distinct Salary from Employee order by Salary desc limit 1,1) as Salary;