转自http://bookshadow.com/weblog/2017/05/05/leetcode-employee-bonus/
题目描述:
Select all employee's name and bonus whose bonus is < 1000.
Table:Employee
+-------+--------+-----------+--------+
| empId | name | supervisor| salary |
+-------+--------+-----------+--------+
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 3 | Brad | null | 4000 |
| 4 | Thomas | 3 | 4000 |
+-------+--------+-----------+--------+
empId is the primary key column for this table.
Table: Bonus
+-------+-------+
| empId | bonus |
+-------+-------+
| 2 | 500 |
| 4 | 2000 |
+-------+-------+
empId is the primary key column for this table.
Example ouput:
+-------+-------+
| name | bonus |
+-------+-------+
| John | null |
| Dan | 500 |
| Brad | null |
+-------+-------+
题目大意:
选出所有奖金<1000元的雇员姓名及奖金数额
解题思路:
左连接(Left Join)
SQL语句:
# Write your MySQL query statement below
SELECT name, bonus
FROM Employee LEFT JOIN Bonus USING (empId)
WHERE IFNULL(bonus, 0 ) < 1000