181. 超过经理收入的员工

发表于 更新于

难度系数： 简单
编写一个SQL查询来查找收入比经理高的员工。
以任意顺序返回结果表。

SQL结构

1
2
3
4
5
6
Create table If Not Exists Employee (id int, name varchar(255), salary int, managerId int)
Truncate table Employee
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')

表：Employee

1
2
3
4
5
6
7
8
9
10
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。

示例 1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
输入: 
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
2
3
4
5
SELECT a.Name AS Employee
FROM Employee AS a, Employee AS b
WHERE a.Salary > b.Salary
AND a.ManagerId = b.Id
AND a.ManagerId IS NOT NULL

原题链接：https://leetcode.cn/problems/employees-earning-more-than-their-managers