LeetCode Database #176 Second Highest Salary
个人比较喜欢小写SQL代码,反正可以写完再格式化
题目内容
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 | +---------------------+
解题
选出第二大的薪水,肯定要做的是排序order by Salary desc
,再使用limit 1 offset 1
来选出第二大的(这个不看solution我还真不知道,数据库还是学的太少)
select distinct Salary as SecondHighestSalary
from Employee
order by Salary desc
limit 1 offset 1;
但是直接这样提交是错误的,因为题目要求当没有结果时返回null
,而空集并不等于null
,参考solution,有两种方法。
第一种是使用ifnull(expr1, expr2)
函数,如果expr1
不为null
返回expr1
,否则返回expr2
。所以可以写作
select ifnull((
select distinct Salary
from Employee
order by Salary desc
limit 1 offset 1),
null) as SecondHighestSalary;
第二种比较骚,select
一个空集的结果为null
。写作
select (
select distinct Salary
from Employee
order by Salary desc
limit 1 offset 1) as SecondHighestSalary;