# Write your MySQL query statement below
181. 超过经理收入的员工
表：Employee
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+ Id是该表的主键。 该表的每一行都表示雇员的ID、姓名、工资和经理的ID。
编写一个SQL查询来查找收入比经理高的员工。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Employee 表: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ 输出: +----------+ | Employee | +----------+ | Joe | +----------+ 解释: Joe 是唯一挣得比经理多的雇员。
原站题解
pythondata 解法, 执行用时: 268 ms, 内存消耗: 60 MB, 提交时间: 2023-09-17 11:00:37
import pandas as pd
def find_employees(Employee: pd.DataFrame) -> pd.DataFrame:
Employee_with_manager = Employee.merge(Employee, left_on='managerId', right_on='id', suffixes=('_emp', '_mgr'))
# 筛选出收入比经理高的员工
Employee_with_higher_salary = Employee_with_manager[Employee_with_manager['salary_emp'] > Employee_with_manager['salary_mgr']]
# 返回结果表，只保留员工的 id 和 name 列
result = Employee_with_higher_salary[[ 'name_emp']].rename(columns={'name_emp': 'Employee'})
return result
def find_employees2(employee: pd.DataFrame) -> pd.DataFrame:
merged_table = pd.merge(
employee,
employee,
how='inner',
left_on='managerId',
right_on='id'
)
result = merged_table.query('salary_x > salary_y')['name_x'].rename('Employee').to_frame()
return result
def find_employees3(employee: pd.DataFrame) -> pd.DataFrame:
df = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left')
df = df[df['salary_x'] > df['salary_y']]
df = df.rename(columns={'name_x': 'Employee'})
return df[['Employee']]
mysql 解法, 执行用时: 1396 ms, 内存消耗: N/A, 提交时间: 2018-08-22 11:41:59
# Write your MySQL query statement below Select e1.Name as Employee from Employee e1 join Employee e2 on e1.ManagerId = e2.Id and e1.Salary > e2.Salary;
mysql 解法, 执行用时: 565 ms, 内存消耗: N/A, 提交时间: 2018-08-22 11:40:50
# Write your MySQL query statement below select e1.Name as Employee from Employee e1, Employee e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary;