杨大伟在路上

大数据第34天—Mysql练习题5-杨大伟

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

数据样式：

IdNameSalaryManagerId
1 Joe 70000 3
2 Henry 80000 4
3 Sam 60000 null
4 Max 90000 null

展示效果：

Employee
Joe 
1 create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int);
2 
3 insert into Employee (Id, Name, Salary, ManagerId) values (1, 'Joe', 70000, 3);
4 insert into Employee (Id, Name, Salary, ManagerId) values (2, 'Henry', 80000, 4);
5 insert into Employee (Id, Name, Salary, ManagerId) values (3, 'Sam', 60000, null);
6 insert into Employee (Id, Name, Salary, ManagerId) values (4, 'Max', 90000, null);

最终SQL:

 1 SELECT
 2      a.NAME AS Employee
 3 FROM 
 4      Employee AS a 
 5 JOIN 
 6      Employee AS b
 7 ON 
 8      a.ManagerId = b.Id 
 9 AND 
10      a.Salary > b.Salary;

 

posted on 2020-08-08 22:28  浪子逆行  阅读(144)  评论(0)  编辑  收藏  举报

导航