数据准备
drop table Employee;
Create table If Not Exists Employee ( empId int , name varchar ( 255 ) , supervisor int , salary int ) ;
Create table If Not Exists Bonus ( empId int , bonus int ) ;
Truncate table Employee;
insert into Employee ( empId, name, supervisor, salary) values ( '3' , 'Brad' , null , '4000' ) ;
insert into Employee ( empId, name, supervisor, salary) values ( '1' , 'John' , '3' , '1000' ) ;
insert into Employee ( empId, name, supervisor, salary) values ( '2' , 'Dan' , '3' , '2000' ) ;
insert into Employee ( empId, name, supervisor, salary) values ( '4' , 'Thomas' , '3' , '4000' ) ;
Truncate table Bonus;
insert into Bonus ( empId, bonus) values ( '2' , '500' ) ;
insert into Bonus ( empId, bonus) values ( '4' , '2000' ) ;
输入
输出
with t1 as (
select e. * ,
if ( b. bonus, b. bonus, 0 ) as b_bonus
from employee e left join Bonus b
on e. empId= b. empId
)
select name,
if ( b_bonus= 0 , null , b_bonus) as bonus
from t1
where b_bonus< 1000