[LeetCode-DATABASE] 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.

题意：查询第二高的工资，没有则返回NULL。

思路1：直接排序去重，这里空要返回NULL，所以用IFNULL()函数。

SQL:

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

 了解到用嵌套的SELECT可以取代IFNULL函数：

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

 

思路2:找到最大的。再在剩下的中找最大的。因为聚集函数可以直接返回NULL

SQL:

select max(Salary) from Employee where Salary < (select max(Salary) from Employee);

 

转载于:https://www.cnblogs.com/TinyBobo/p/4597951.html