# Write your MySQL query statement below
185. 部门工资前三高的所有员工
表: Employee
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ Id是该表的主键列。 departmentId是Department表中ID的外键。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ Id是该表的主键列。 该表的每一行表示部门ID和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写一个SQL查询，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释: 在IT部门: - Max的工资最高 - 兰迪和乔都赚取第二高的独特的薪水 - 威尔的薪水是第三高的 在销售部: - 亨利的工资最高 - 山姆的薪水第二高 - 没有第三高的工资，因为只有两名员工
原站题解
pythondata 解法, 执行用时: 415 ms, 内存消耗: 67.2 MB, 提交时间: 2024-05-27 09:40:17
import pandas as pd
def top_three_salaries(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
# 合并Employee和Department表格
merged_df = pd.merge(employee, department, left_on='departmentId', right_on='id', how='inner')
# 使用rank方法计算每个部门工资的排名
merged_df['rnk'] = merged_df.groupby('name_y')['salary'].rank(method='dense', ascending=False)
# 筛选出排名前三的记录
result_df = merged_df[merged_df['rnk'] <= 3][['name_y', 'name_x', 'salary']]
return result_df.rename({
'name_y':'Department',
'name_x':'Employee',
'salary':'Salary'
}, axis=1)
pythondata 解法, 执行用时: 442 ms, 内存消耗: 67.9 MB, 提交时间: 2024-05-27 09:39:42
import pandas as pd
def top_three_salaries(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
# 合并数据
merged_df = employee.merge(department, left_on='departmentId', right_on='id', how='inner')
# 使用rank方法得到每个部门的工资排名
merged_df['rank'] = merged_df.groupby('departmentId')['salary'].rank(method='dense', ascending=False)
# 选择排名前三的员工
result_df = merged_df[merged_df['rank'] <= 3]
#print(result_df) 检查所需重命名的列
# 选择并重命名所需的列
result_df = result_df[['name_y', 'name_x', 'salary']]
result_df.columns = ['Department', 'Employee', 'Salary']
return result_df.sort_values(by=['Department', 'Salary'], ascending=[True, False])
mysql 解法, 执行用时: 2368 ms, 内存消耗: N/A, 提交时间: 2018-08-22 15:32:53
# 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 (select count(distinct Salary) from Employee e2 where e2.DepartmentId=d.Id and e2.Salary > e.Salary) < 3 order by Department;