Mysql-Leetcode
MySQL-Demos

second-highest-salary

难点:
  • 第二高薪水 (distinct + limit + offset)
  • 如果没有则返回 NULL (outer select)
1
select
2
(
3
select
4
distinct Salary
5
from
6
Employee
7
order by
8
Salary desc limit 1 offset 1
9
)
10
as SecondHighestSalary;
11
12
-- or
13
SELECT max(Salary)
14
FROM Employee
15
WHERE Salary < (SELECT max(Salary) FROM Employee)
16
17
-- bad answer
18
select
19
Salary as SecondHighestSalary
20
from
21
Employee
22
order by
23
Salary desc limit 1 offset 1;
24
-- 如果没有第二高的薪水,则返回空 "",使用 max 函数可以返回 NULL,outer select 在查询不到的时候也会返回空。如果第二高的薪水一定存在,则用 limit 最好
Copied!
Last modified 12d ago
Copy link