打怪升级-176.第二高薪水

题目描述

Employee 表：
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。
示例1：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例2：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

解答

方法一：

select max(salary) as SecondHighestSalary from Employee where salary<(select max(salary) from Employee)

方法二：

select (select distinct salary from Employee order by salary desc limit 1,1) as SecondHighestSalary 

方法三：（注意：mysql8及以上才可以用开窗函数）

select(
select salary from (select distinct salary,rank() over(order by salary desc) rk from Employee ) a where rk = 2
) as SecondHighestSalary