181. 超过经理收入的员工

🟢   🔖  数据库  🔗 力扣 LeetCode

题目

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+

id is the primary key (column with unique values) for this table.

Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.

Write a solution to find the employees who earn more than their managers.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:

Employee table:

+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+

Output:

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

Explanation: Joe is the only employee who earns more than his manager.

题目大意

表：Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+

id 是该表的主键（具有唯一值的列）。

该表的每一行都表示雇员的 ID、姓名、工资和经理的 ID。

编写解决方案，找出收入比经理高的员工。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 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 是唯一挣得比经理多的雇员。

解题思路

1. SELECT e.name AS Employee：查询结果中只显示员工姓名，并使用别名 Employee。

2. 为了比较员工和经理的薪资，我们需要将 Employee 表自己连接。

   • FROM Employee e INNER JOIN Employee m：对 Employee 表进行自连接，分别用别名 e 和 m 表示员工与经理。

3. 在连接条件中，通过 e.managerId = m.id，实现员工与其经理的关联。

4. 使用 WHERE 条件，筛选出 e.salary > m.salary 的记录，表示员工薪资高于经理。

复杂度分析

• 时间复杂度：O(n^2)，其中 n 是 Employee 表的行数。由于是自连接，每个员工需要与所有可能的经理匹配。
• 空间复杂度：O(n)，存储连接结果所需的空间。

代码

SELECT e.name AS Employee
FROM Employee e
INNER JOIN Employee m
ON e.managerId = m.id
WHERE e.salary > m.salary