LeetCode 176. Second Highest Salary

Description：

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

Solution：

方法一：

select distinct 保证只列出一个值，order by desc 保证降序排序
limit 2,3 表示从跳过2条数据，查询3个数据。limit 2 offset 3 表示跳过3条数据，查询2条数据
ifnull(expression, value) 表示如果为 null 则返回value，如果非空返回表达式

//在select选出的临时表上继续选择，这样不会爆 null 错误
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

//另一种解决不报 null 错误的方法使用ifnull
select
	IFNULL(
		(select distinct Salary
			from Employee
			order by Salary DESC
			limit 1 offset 1), NULL) AS SecondHighestSalary
	

方法二：

实际使用中可能用处不大，但是可以练习sql语句

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee 
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)