超过经理收入的员工——mysql面试题
需求:Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。
数据样式:
展示效果:
create table If Not Exists 5_Employee (Id int, Name varchar(255), Salary int, ManagerId int);
truncate table 5_Employee;
insert into 5_Employee (Id, Name, Salary, ManagerId) values (1, 'Joe', 70000, 3);
insert into 5_Employee (Id, Name, Salary, ManagerId) values (2, 'Henry', 80000, 4);
insert into 5_Employee (Id, Name, Salary, ManagerId) values (3, 'Sam', 60000, null);
insert into 5_Employee (Id, Name, Salary, ManagerId) values (4, 'Max', 90000, null);
最终SQL:
SELECT
e1.Name as Employee
FROM
5_Employee e1
inner join
5_Employee e2
on
e1.Managerld = e2.Id
and
e1.salary > e2.salary;