力扣 577.员工奖金

最新推荐文章于 2024-07-15 21:11:11 发布

奋斗哼哼

最新推荐文章于 2024-07-15 21:11:11 发布

阅读量64

点赞数

文章标签： leetcode 算法 sql

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130369525

版权

数据准备

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