大数据第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;