comments | difficulty | edit_url | tags | |
---|---|---|---|---|
true |
中等 |
|
Employee
表:
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id 是这个表的主键。 表的每一行包含员工的工资信息。
查询并返回 Employee
表中第二高的 不同 薪水 。如果不存在第二高的薪水,查询应该返回 null(Pandas 则返回 None)
。
查询结果如下例所示。
示例 1:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 输出: +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
示例 2:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ 输出: +---------------------+ | SecondHighestSalary | +---------------------+ | null | +---------------------+
我们可以按照薪水降序排列,然后使用 LIMIT
语句来获取第二高的薪水,如果不存在第二高的薪水,那么就返回 null
。
import pandas as pd
def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:
# Drop any duplicate salary values to avoid counting duplicates as separate salary ranks
unique_salaries = employee["salary"].drop_duplicates()
# Sort the unique salaries in descending order and get the second highest salary
second_highest = (
unique_salaries.nlargest(2).iloc[-1] if len(unique_salaries) >= 2 else None
)
# If the second highest salary doesn't exist (e.g., there are fewer than two unique salaries), return None
if second_highest is None:
return pd.DataFrame({"SecondHighestSalary": [None]})
# Create a DataFrame with the second highest salary
result_df = pd.DataFrame({"SecondHighestSalary": [second_highest]})
return result_df
# Write your MySQL query statement below
SELECT
(
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1, 1
) AS SecondHighestSalary;
我们也可以使用 MAX()
函数,从小于 MAX()
的薪水中挑选一个最大的薪水即可。
# Write your MySQL query statement below
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
我们还可以先通过 DENSE_RANK()
函数计算出每个员工的薪水排名,然后再筛选出排名为
# Write your MySQL query statement below
WITH T AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM Employee)
SELECT (SELECT DISTINCT salary FROM T WHERE rk = 2) AS SecondHighestSalary;