Leetcode# 1873. Calculate Special Bonus

SQL架构:

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');
;

Column Name	Type
employee_id	int
name	varchar
salary	int

employee_id is the primary key for this table.

Write an SQL query to calculate the bonus of each employee.

如果员工的ID是奇数，并且员工的名字不是以字符“M”开头，则员工的奖金是其工资的100%。否则，员工的奖金为0。

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:

employeeBonus

Employee_id	bouns
2	0
3	0
7	7400
8	0
9	7700

方法一: 根据题目要求来说对employee_id取余并适用关键函数"LIKE"

SQL LIKE 子句中使用百分号 %字符来表示任意字符

此题中用"NOT"对 LIKE"M%" 进行取反是本方法的关键

SELECT
    employee_id,
    IF(
                        employee_id % 2 = 1 AND
                        name NOT LIKE 'M%',
                        salary, 0
        ) AS bonus
FROM
    Employees
ORDER BY
    employee_id;

方法二:

本方法用"MOD"函数,本函数是值MOD(x,y)中返回 x 除以 y 以后的余数.

这也可用于判断,MOD(x,2)=0是偶数MOD(x,2)!=0则是奇数

"LEFT(s,n)"函数是返回字符串 s 的前 n 个字符,LEFT(name,1)='M'是用于对name字符串第一个字母进行筛选.

此外本题还有个"

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

" 

其中CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果

condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不

执行了. 其实属于条件语句.

SELECT
    employee_id,
    (CASE
         WHEN MOD(employee_id, 2) != 0 AND LEFT(name, 1) != 'M' THEN
             salary
         WHEN MOD(employee_id, 2) = 0 OR LEFT(name, 1) = 'M' THEN 0
        END)
        AS bonus
FROM
    Employees
ORDER BY
    employee_id;