177. Nth Highest Salary
表格：Employee
+-------------+------+
| 列名 | 類型 |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id 是此表格的主鍵（具有唯一值的列）。 此表的每一行包含一名員工的薪資信息。
撰寫一個解決方案來從 Employee 表中查找第 n 高的薪資。如果沒有第 n 高的薪資，則返回 null。
結果格式如下例所示。
範例：
輸入： Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
輸出：
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
MySQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE result INT;
SET N = N - 1;
SELECT DISTINCT salary INTO result
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET N;
RETURN result;
END
402ms
Pandas
import pandas as pd
def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
if employee.empty or N <= 0:
return pd.DataFrame({f'getNthHighestSalary({N})': [None]})
# Remove duplicate salary values and sort them in descending order
unique_salaries = employee['salary'].drop_duplicates().sort_values(ascending=False)
# If the number of unique salaries is less than N, return a DataFrame with None
if len(unique_salaries) < N:
return pd.DataFrame({f'getNthHighestSalary({N})': [None]})
# Get the Nth highest salary
nth_highest = unique_salaries.iloc[N-1]
# Return a DataFrame with the Nth highest salary
return pd.DataFrame({f'getNthHighestSalary({N})': [nth_highest]})
66.04MB, 471ms
PostgreSQL
CREATE OR REPLACE FUNCTION NthHighestSalary(N INT) RETURNS TABLE (Salary INT) AS $$
BEGIN
RETURN QUERY (
SELECT DISTINCT e.salary
FROM Employee e
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM Employee e2
WHERE e2.salary > e.salary
) = N - 1
LIMIT 1
);
END;
$$ LANGUAGE plpgsql;
0MB, 718ms
