Leetcode 176 Second Highest Salary SQL Pandas解法

题目

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)