Leetcode 181. Employees Earning More Than Their Managers | SQL Pandas 解法_given the table ,write a sql query that finds out -优快云博客

本文链接：https://blog.youkuaiyun.com/Dandelion63/article/details/106655050

本文介绍了一种SQL查询方法，用于找出薪资高于其直接上级（经理）的员工。此外，还展示了如何使用Pandas库在Python中实现相同的功能。以Employee表为例，我们使用了内连接（INNER JOIN）来匹配员工及其经理的记录，并通过WHERE子句筛选出薪资较高的员工。同时，使用Pandas的merge函数实现了类似SQL内连接的效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Leetcode 181. Employees Earning More Than Their Managers | SQL Pandas 解法

Easy

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

SQL

where 语句

select e1.name as Employee
from employee e1, employee e2
where e1.managerId = e2.Id
and e1.salary > e2.salary

inner join

select e1.name as Employee
from employee e1
join employee e2
on e1.managerId = e2.Id
and e1.salary > e2.salary

Pandas

Merge: inner join
Subset: employee salary > manager salary

data = [[1, "Joe", 70000, 3], [2, "Henry", 80000, 4], [3, "Sam", 60000, None], [4, "Max", 90000, None]]
employee = pd.DataFrame(data, columns = ["Id", "Name", "Salary", "ManagerId"])
employee

emp_mgr = pd.merge(employee, employee, left_on='ManagerId', right_on='Id', how='inner')
emp_mgr[emp_mgr['Salary_x'] > emp_mgr['Salary_y']][['Name_x']].rename(columns={'Name_x':'Employee'})

# emp_mgr[['Name_x']].set_axis(['Employee'], axis='columns', inplace=False)