LeetCode MySQL-1873. Calculate Special Bonus

    AllenLi
    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

    Related Posts