跳至主要內容

177. 第N高的薪水


177. 第 N 高的薪水

🟠   🔖  数据库  🔗 力扣open in new window LeetCodeopen in new window

题目

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+

id is the primary key (column with unique values) for this table.

Each row of this table contains information about the salary of an employee.

Write a solution to find the nth highest salary from the Employee table. If there is no nth highest salary, return null.

The result format is in the following example.

Example 1:

Input:

Employee table:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

n = 2

Output:

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Example 2:

Input:

Employee table:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2

Output:

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

题目大意

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+

在 SQL 中,id 是该表的主键。

该表的每一行都包含有关员工工资的信息。

查询 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询结果应该为 null

查询结果格式如下所示。

示例 1:

输入:

Employee 表:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2

输出:

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

示例 2:

输入:

Employee 表:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2

输出:

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

解题思路

  • 去重排序:提取工资列,使用 drop_duplicates() 去重,并按降序排序。
  • 检查索引:根据 n 的值定位到排序后工资列表的第 n 个元素。如果 n 超过工资列表长度,返回 null

复杂度分析

  • 时间复杂度O(n log n)
    • drop_duplicatesO(n),提取唯一工资。
    • sort_valuesO(n log n),对工资进行排序。
    • 总体复杂度为 O(n log n)
  • 空间复杂度drop_duplicates 和排序操作需要额外存储唯一工资列表,空间复杂度为 O(n)

代码

import pandas as pd

def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
    # 提取唯一工资并降序排序
    sorted_salary = employee['salary'].drop_duplicates().sort_values(ascending=False)
    # 若 n 超过范围则返回 null
    if N > len(sorted_salary) or N <= 0:
        return pd.DataFrame({f'getNthHighestSalary({N})':[pd.NA]})
    # 返回第 n 高工资
    else:
        return pd.DataFrame({f'getNthHighestSalary({N})':[sorted_salary.iloc[N - 1]]})

相关题目

题号标题题解标签难度力扣
2205有资格享受折扣的用户数量 🔒数据库🟢🀄️open in new window 🔗open in new window