2021-04-13

员工奖金—mysql中left运用

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

展示效果：

name	bonus
John	null
Dan	500
Brad	null

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

最终sql：

SELECT
    e.name, 
    b.bonus
FROM
    17_Employee e
LEFT JOIN
    17_Bonus b
ON 
    e.empid = b.empid
WHERE
    bonus < 1000 OR bonus IS NULL;