大数据第59天—MySQL之员工奖金-杨大伟

需求:选出所有 bonus < 1000 的员工的 name 及其 bonus。

展示效果:

name bonus
John null
Dan 500
Brad null
 1 Create table If Not Exists 17_Employee (EmpId int, Name varchar(255), Supervisor int, Salary int);
 2 Create table If Not Exists 17_Bonus (EmpId int, Bonus int);
 3 Truncate table 17_Employee;
 4 insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (3, Brad, null, 4000);
 5 insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (1, John, 3, 1000);
 6 insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (2, Dan, 3, 2000);
 7 insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (4, Thomas, 3, 4000);
 8 Truncate table 17_Bonus;
 9 insert into 17_Bonus (EmpId, Bonus) values (2, 500);
10 insert into 17_Bonus (EmpId, Bonus) values (4, 2000);

 

最终SQL:

 1 SELECT
 2     e.name, 
 3     b.bonus
 4 FROM
 5     17_Employee e
 6 LEFT JOIN
 7     17_Bonus b
 8 ON 
 9     e.empid = b.empid
10 WHERE
11     bonus < 1000 OR bonus IS NULL;

 

大数据第59天—MySQL之员工奖金-杨大伟

上一篇:mysql


下一篇:mysql常见问题及汇总处理