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