# Write your MySQL query statement below
184. 部门工资最高的员工
表： Employee
+--------------+---------+ | 列名 | 类型 | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id是此表的主键列。 departmentId是Department表中ID的外键。 此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。
表： Department
+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id是此表的主键列。 此表的每一行都表示一个部门的ID及其名称。
编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
示例 1:
输入： Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出： +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。
原站题解
pythondata 解法, 执行用时: 416 ms, 内存消耗: 62.2 MB, 提交时间: 2023-08-09 17:37:22
import pandas as pd
def department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
# 将 Employee 表和 Department 表进行连接
merged_data = pd.merge(employee, department, left_on='departmentId', right_on='id')
# 获取每个部门中薪资最高的员工的信息
highest_salary_per_department = merged_data.groupby('name_y').apply(lambda x: x[x['salary'] == x['salary'].max()]).reset_index(drop=True)
# 按照指定顺序选择列
result = highest_salary_per_department[['name_y', 'name_x', 'salary']]
# 重命名列名
result.columns = ['Department', 'Employee', 'Salary']
return result
mysql 解法, 执行用时: 2638 ms, 内存消耗: N/A, 提交时间: 2018-08-22 14:55:08
# Write your MySQL query statement below select d.Name as Department,e.Name as Employee, e.Salary from Department d, Employee e where e.DepartmentId=d.Id and e.Salary=(select max(Salary) from Employee e2 where e2.DepartmentId=d.Id);