Leetcode 176. Second Highest Salary (Database)

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.

思路：解法1.找出最高的salary, 所有小于最高salary中的最大值就是第二高的salary。

# Write your MySQL query statement below
select max(Salary) as SecondHighestSalary from Employee 
where Salary < (select max(Salary) from Employee)

解法2. 参考 http://tsuinte.ru/2015/04/05/leetcode-database-176-second-highest-salary

构造一个tmp，从里面选，如果tmp为空则返回null

1 # Write your MySQL query statement below
2 select 
3     if(count(Salary) >= 1, Salary, null) as SecondHighestSalary 
4 from (select distinct salary from Employee 
5 order by Salary desc limit 1,1) tmp

 

转载于:https://www.cnblogs.com/lettuan/p/6289357.html