577题. 员工奖金
SQL架构:
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', 'None', '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')
问题:选出所有 bonus < 1000 的员工的 name 及其 bonus。
Employee 表单
empId | name | supervisor | salary |
---|---|---|---|
1 | John | 3 | 1000 |
2 | Dan | 3 | 2000 |
3 | Brad | null | 4000 |
4 | Thomas | 3 | 4000 |
empId 是这张表单的主关键字
Bonus 表单
empId | bonus |
---|---|
2 | 500 |
4 | 2000 |
empId 是这张表单的主关键字
输出示例:
name | bonus |
---|---|
John | null |
Dan | 500 |
Brad | null |
解答:
select name,bonus from Employee a1
left join Bonus a2
on a1.empId = a2.empId
where a2.bonus < 1000 or a2.bonus is null;
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/employee-bonus