Leetcode: Second Highest Salary

最新推荐文章于 2021-01-14 16:38:49 发布

ayst123

最新推荐文章于 2021-01-14 16:38:49 发布

阅读量366

点赞数

CC 4.0 BY-SA版权

分类专栏： leetcode

本文链接：https://blog.youkuaiyun.com/ayst123/article/details/45718571

leetcode 专栏收录该内容

216 篇文章

订阅专栏

本文介绍了一种使用SQL从员工表中查询第二高薪水的方法。提供了两种解决方案：一种是通过寻找次于最高薪水的最大值；另一种是利用左连接避免空值出现的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Get idea from here

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 second highest salary is 200. If there is no second highest salary, then the query should return null.

Other′s Solution

v1.

Second one should be less than Max one

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

v2.

using left join will avoid that null does not appear. We make a observation that the max one will not appear in e2

# Write your MySQL query statement below
select e2.Salary
from Employee e1 left join Employee e2 on e1.Salary>e2.Salary
order by e2.Salary desc
limit 1