177. Nth 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.
getNthHighestSalary(2) |
---|
200 |
解法
这道题和176本质是一样的,思路请移步这里。这道题需要我们写成一个function来解决此类问题。
- 首先我们定义一个变量,M = N - 1,写在最前面,主要是因为选择第N个数值,需要
offset
N-1个数值 - 其次使用limit offset来选取第N个数值
SQL
解法一:limit offset
使用function之前的LC 176的解法是这样的
select ifnull(
(
select distinct salary from employee
limit 1 offset 1
)
, NULL) as SecondHighestSalary
定义function之后是这样的
CREATE FUNCTION getNthHighestSalar