LeetCode--577. 员工奖金

1 题目描述

表: Employee

+-------------+---------+  
| Column Name | Type    |  
+-------------+---------+  
| empId       | int     |  
| name        | varchar |  
| supervisor  | int     |  
| salary      | int     |  
+-------------+---------+  

empId 是该表中具有唯一值的列.
该表的每一行都表示员工的姓名和 id, 以及他们的工资和经理的 id.

表: Bonus

+-------------+------+  
| Column Name | Type |  
+-------------+------+  
| empId       | int  |  
| bonus       | int  |  
+-------------+------+  

empId 是该表具有唯一值的列.
empId 是 Employee 表中 empId 的外键 (reference 列).
该表的每一行都包含一个员工的 id 和他们各自的奖金.

编写解决方案, 报告每个奖金少于 1000 的员工的姓名和奖金数额.
以任意顺序 返回结果表.

2 测试用例

**示例 1: **

**输入: **
Employee table:

+-------+--------+------------+--------+  
| empId | name   | supervisor | salary |  
+-------+--------+------------+--------+  
| 3     | Brad   | null       | 4000   |  
| 1     | John   | 3          | 1000   |  
| 2     | Dan    | 3          | 2000   |  
| 4     | Thomas | 3          | 4000   |  
+-------+--------+------------+--------+  

Bonus table:

+-------+-------+  
| empId | bonus |  
+-------+-------+  
| 2     | 500   |  
| 4     | 2000  |  
+-------+-------+  

**输出: **

+------+-------+  
| name | bonus |  
+------+-------+  
| Brad | null  |  
| John | null  |  
| Dan  | 500   |  
+------+-------+  

3 解题思路

题目要求: 奖金少于 1000 的员工的姓名和奖金数额

1. 使用左外连接, 查询员工和奖金信息

select e.name, b.bonus  
from Employee as e  
         left join Bonus as b on e.empId = b.empId;  

查询结果

name	bonus
John	null
Dan	500
Brad	null
Thomas	2000

2. 奖金数额小于 1000, 对查询的数据过滤, 从测试用例看, 没有奖金的也要考虑进来

select e.name, b.bonus  
from Employee as e  
         left join Bonus as b on e.empId = b.empId  
where b.bonus < 1000  
   or b.bonus is null;

查询结果

name	bonus
John	null
Dan	500
Brad	null

3.1 解法 1:

select e.name, b.bonus  
from Employee as e  
         left join Bonus as b on e.empId = b.empId  
where b.bonus < 1000  
   or b.bonus is null;

查询结果

name	bonus
John	null
Dan	500
Brad	null

4 相似题目

LeetCode–175. 组合两个表