第二高的薪水

176. 第二高的薪水

Employee 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

查询结果如下例所示。

示例 1：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例 2：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

原站题解

去查看

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

# Write your MySQL query statement below

pythondata 解法, 执行用时: 248 ms, 内存消耗: 60 MB, 提交时间: 2023-08-09 17:36:24

import pandas as pd

def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:
    # 1. 删除所有重复的薪水.
    employee = employee.drop_duplicates(["salary"])
    
    # 2. 如果少于 2 个不同的薪水，返回 `np.NaN`。
    if len(employee["salary"].unique()) < 2:
        return pd.DataFrame({"SecondHighestSalary": [np.NaN]})
    
    # 3. 把表格按 `salary` 降序排序。
    employee = employee.sort_values("salary", ascending=False)
    
    # 4. 删除 `id` 列。
    employee.drop("id", axis=1, inplace=True)
    
    # 5. 重命名 `salary` 列。
    employee.rename({"salary": "SecondHighestSalary"}, axis=1, inplace=True)
    
    # 6, 7. 返回第 2 高的薪水
    return employee.head(2).tail(1)

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

import pandas as pd

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

mysql 解法, 执行用时: 251 ms, 内存消耗: 0 B, 提交时间: 2022-06-01 10:17:14

# Write your MySQL query statement below
select ifnull((select distinct salary from employee order by salary desc limit 1 offset 1), null) as SecondHighestSalary

mysql 解法, 执行用时: 226 ms, 内存消耗: 0 B, 提交时间: 2022-06-01 10:13:43

# Write your MySQL query statement below
select Salary as SecondHighestSalary from (select Salary from Employee group by Salary union all (select null as Salary) order by Salary desc limit 1 offset 1) E;

mysql 解法, 执行用时: 243 ms, 内存消耗: N/A, 提交时间: 2018-08-22 11:20:13

# Write your MySQL query statement below
# union all 用于合并多个select语句的结果集, 允许重复的值
select Salary as SecondHighestSalary from (select Salary from Employee group by Salary union all (select null as Salary) order by Salary desc limit 1 offset 1) E;

mysql 解法, 执行用时: 291 ms, 内存消耗: N/A, 提交时间: 2018-08-22 11:12:34

# Write your MySQL query statement below
select max(Salary) as SecondHighestSalary from Employee where Salary not in (select max(Salary) from Employee);

上一题

下一题

详情