第N高的薪水

177. 第N高的薪水

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

查询结果格式如下所示。

示例 1:

输入: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

示例 2:

输入: 
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

-CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT -BEGIN - RETURN ( - # Write your MySQL query statement below. - - ); -END

pythondata 解法, 执行用时: 288 ms, 内存消耗: 60.1 MB, 提交时间: 2023-08-09 17:34:55

import pandas as pd

def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
    df = employee[["salary"]].drop_duplicates()
    if len(df) < N:
        return pd.DataFrame({'getNthHighestSalary(2)': [None]})
    return df.sort_values("salary", ascending=False).head(N).tail(1)

pythondata 解法, 执行用时: 320 ms, 内存消耗: 59.8 MB, 提交时间: 2023-08-09 17:34:37

import pandas as pd

def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
    # 按照工资降序排序
    employee = employee.sort_values(by='salary', ascending=False)
    
    # 去除重复的工资
    employee = employee.drop_duplicates(subset=['salary'])
    
    # 选择第N高的工资，如果不存在则返回null
    if len(employee) >= N:
        return pd.DataFrame({'getNthHighestSalary': [int(employee.iloc[N-1]['salary'])]})
    else:
        return pd.DataFrame({'getNthHighestSalary': [None]})

mysql 解法, 执行用时: 868 ms, 内存消耗: N/A, 提交时间: 2018-08-22 16:07:48

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select max(Salary) from Employee E1 where N - 1 = (select count(distinct E2.Salary) from Employee E2 where E2.Salary > E1.Salary)
  );
END

mysql 解法, 执行用时: 375 ms, 内存消耗: N/A, 提交时间: 2018-08-22 16:05:33

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set n = n - 1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary FROM Employee order by Salary desc limit 1 offset n
  );
END

上一题

下一题

详情