184. Department Highest Salary
員工表
+--------------+---------+
| 列名 | 類型 |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id 是這個表的主鍵（具有唯一值的列）。 departmentId 是一個外鍵（引用列），引用自 Department 表的 ID。 這個表的每一行表示一個員工的 ID、姓名、薪水以及他們所在部門的 ID。
部門表
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id 是這個表的主鍵（具有唯一值的列）。保證部門名稱不為 NULL。 這個表的每一行表示一個部門的 ID 和名稱。
解題說明
撰寫一個解決方案來查找每個部門中薪水最高的員工。
可以按任意順序返回結果表。
+--------------+---------+
| 列名 | 類型 |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id 是這個表的主鍵（具有唯一值的列）。 departmentId 是一個外鍵（引用列），引用自 Department 表的 ID。 這個表的每一行表示一個員工的 ID、姓名、薪水以及他們所在部門的 ID。
部門表
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id 是這個表的主鍵（具有唯一值的列）。保證部門名稱不為 NULL。 這個表的每一行表示一個部門的 ID 和名稱。
解題說明
撰寫一個解決方案來查找每個部門中薪水最高的員工。
可以按任意順序返回結果表。
範例：
輸入：
員工表：
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
部門表：
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
輸出：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
解釋：
Max 和 Jim 都在 IT 部門中有最高薪水，Henry 在 Sales 部門中有最高薪水。
輸入：
員工表：
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
部門表：
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
輸出：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
解釋：
Max 和 Jim 都在 IT 部門中有最高薪水，Henry 在 Sales 部門中有最高薪水。
MySQL
# Write your MySQL query statement below
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE (e.salary, e.departmentId) IN (
SELECT MAX(salary), departmentId
FROM Employee
GROUP BY departmentId
);
3055ms
Pandas
import pandas as pd
def department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
# Merge the employee and department DataFrames
merged_df = employee.merge(department, left_on='departmentId', right_on='id', suffixes=('', '_dept'))
# Find the highest salary in each department
highest_salaries = merged_df.groupby('departmentId')['salary'].max().reset_index()
# Merge the highest salaries back to the original merged DataFrame
result_df = merged_df.merge(highest_salaries, on=['departmentId', 'salary'])
# Select and rename the necessary columns
result_df = result_df[['name_dept', 'name', 'salary']]
result_df.columns = ['Department', 'Employee', 'Salary']
return result_df
67.87MB, 539ms
PostgreSQL
-- Write your PostgreSQL query statement below
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE (e.salary, e.departmentId) IN (
SELECT MAX(salary), departmentId
FROM Employee
GROUP BY departmentId
);
304ms
