1873. Calculate Special Bonus

題目敘述

SQL Schema >

Create table If Not Exists Employees (employee_id int, name varchar(30), salary int)
Truncate table Employees
insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000')
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3800')
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400')
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100')
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7700')

Table: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee ID, employee name, and salary.

Write an SQL query to calculate the bonus of each employee. The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee's name does not start with the character 'M'. The bonus of an employee is 0 otherwise.

Return the result table ordered by employee_id.

The query result format is in the following example.

Example 1:

Input:
Employees table:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
Output:
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
Explanation:
The employees with IDs 2 and 8 get 0 bonus because they have an even employee_id.
The employee with ID 3 gets 0 bonus because their name starts with 'M'.
The rest of the employees get a 100% bonus.

題目翻譯

從資料表 Employees 計算出每個員工的獎金，其中如果員工的 ID 是奇數且員工名稱字首不是 M 開頭，則獎金會為 0。最後回傳員工 ID 和獎金。

解法解析

這題需要使用條件式的判斷來選擇最後要回傳的獎金是不是 0，這裡範例使用了 IF 函數和 CASE 來處理。

解法範例

SQL

# Write your MySQL query statement below
SELECT employee_id,
    IF(
        employee_id % 2 = 1
        AND name NOT LIKE 'M%',
        salary,
        0
    ) AS bonus
FROM `Employees`
ORDER BY employee_id;

# Write your MySQL query statement below
SELECT employee_id,
    CASE
        WHERE employee_id %2 <> 0
            AND name NOT LIKE 'M%' THEN salary
            ELSE 0
    END AS bonus
FROM `Employees`
ORDER BY employee_id;