题目
Easy
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 |
解法
解答这道题需要考虑两点:1)第二高的工资是否存在;2)如果存在,结果会不会存在并列的情况。这里我们用limit
和offset
来寻找第二高的工资。
offset k
会将前k
行跳过limit j
限定返回结果中的前j
行
这里先去重(distinct
),降序排序,再从结果中从第二行开始选择第一个结果。如果结果不存在,将结果设置为null
SQL
解法一
select (
select distinct salary
from employee
order by Salary DESC
limit 1
offset 1) as SecondHighestSalary
解法二
select ifnull(
(
select distinct salary from employee
limit 1 offset 1
)
, NULL) as SecondHighestSalary
解法三
select ifnull(max(Salary), NULL) as SecondHighestSalary
from Employee
where Salary not in (select max(Salary) as maxSalary from Employee)
Pandas
- 建立Salary表
- 降序排列Salary列
- 使用
dense rank
计算排序,意味着数值并列时,排名相同,并且连续。例如,5,5,4,3,3的排序为1,1,2,3,3 - 选取排序为2的,并且将列重命名为
SecondHighestSalary
import pandas as pd
# create pandas data frame
df = pd.DataFrame({'ID': [1,2,3,4,5,6], 'Salary': [100, 200, 300, 300, 200, 200]})
# sort by descending
df[df.Salary != df.Salary.max()].sort_values(by = 'Salary', ascending = False)
# create dense rank
df['Rank'] = df.Salary.rank(method='dense').astype('int')
# subset and rename
df[df.Rank == 2]['Salary'].to_frame().rename(columns={'Salary':'SecondHighestSalary'}).drop_duplicates().reset_index(drop=True)
简单方法,一步到位 😃
df.Salary.nlargest(2)