176. Second Highest Salary

Question

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

Thinking:

Method:
- 使用limit， order by, ifnull组合
- ifnull的使用方法
  - select ifnull(expression1, expression2) newname;
  - 如果expression1是null，则显示expression1，而字段名是newname。

# Write your MySQL query statement below
select IFNULL(
(select distinct
    Salary
from
    Employee
order by
    Salary desc
limit
    1, 1), NULL) SecondHighestSalary;

Method 2:
- 两次查询，先查出最大值，再通过not in查出第二大的值。

select
    max(Salary) as SecondHighestSalary 
from
    Employee
where
    Salary
not in(
    select max(Salary) from Employee
)

二刷

通过子查询实现筛选出第二大的数。
通过IFNULL判断，如果不存在则填写NULL。
使用DISTINCT去重。

# Write your MySQL query statement below
SELECT IFNULL(
(SELECT distinct Salary
FROM Employee
ORDER BY Salary desc limit 1,1)
, null) as SecondHighestSalary ;