LeetCode MySQL-1873. Calculate Special Bonus

    LeetCode MySQL

    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 name does not start with the character ‘M’. The bonus of an employee is 0 otherwise.
    Return the result table ordered by employee_id.

    寫一個 SQL Query來計算,每一位員工的「獎金」,如果員工的ID為奇數,
    且員工名字不是'M'開頭,則員工獎金為 100% ,
    否則員工的獎金為0,並依照「emplyee_id」回傳排序的結果。
    

    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.
    

    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.
    

    Solution:
    1. 選擇標題「employee_id」、「bonus」
    2. 運用「CASE WHEN」判定符合兩個條件其中一下時,bonus為 0 ,否則為正常的salary。
    – name 開頭為 ‘M%’ (用%來允許後方可有多個字元)
    – employee_id為偶數 (使用 %2 = 0 來排除奇數)
    3. 來自於 Employees 的 table
    4. 將「employee_id」進行排序

    Code:

    SELECT 
      employee_id,
      (CASE WHEN (name LIKE 'M%' 
                 OR employee_id % 2 = 0) THEN 0 ELSE salary END) AS 'bonus'
      FROM Employees
    ORDER BY employee_id;
    

    newEmployees