mysql将收入增加200_【LeetCode】mysql练习——超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+

| Id | Name | Salary | ManagerId |

+----+-------+--------+-----------+

| 1 | Joe | 70000 | 3 |

| 2 | Henry | 80000 | 4 |

| 3 | Sam | 60000 | NULL |

| 4 | Max | 90000 | NULL |

+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+

| Employee |

+----------+

| Joe |

+----------+

mysql脚本

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', null);

insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', null);

自己先动动脑子

答案:

#自己写的

SELECT

e.`Name` Employee

FROM

employee01 e

LEFT JOIN employee01 ep ON e.ManagerId = ep.Id

WHERE

e.ManagerId IS NOT NULL

AND e.Salary > ep.Salary

#答案

SELECT

a. NAME AS Employee

FROM

Employee01 AS a

JOIN Employee01 AS b ON a.ManagerId = b.Id

AND a.Salary > b.Salary

SELECT

a.name AS Employee

FROM employee01 AS a,employee01 AS b

where a.ManagerId = b.Id

AND a.Salary > b.Salary

题目来源:

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/description/