.jpg)
表：Employees
写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头，那么他的奖金是他工资的100%，否则奖金为0。
Return the result table ordered by employee_id.
来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/calculate-special-bonus
解法1（IF）
SELECT
employee_id,
IF ( employee_id % 2 = 0 OR NAME LIKE 'M%', 0, salary ) AS bonus
FROM
Employees
ORDER BY
employee_id
解法2（UNION）
SELECT employee_id, salary AS bonus
FROM Employees
WHERE MOD(employee_id, 2) = 1 AND name NOT LIKE 'M%'
UNION
SELECT employee_id, 0 AS bonus
FROM Employees
WHERE MOD(employee_id, 2) = 0 OR name LIKE 'M%'
ORDER BY
employee_id
解法3（CASE）
SELECT
employee_id,
(
CASE
WHEN MOD ( employee_id, 2 ) = 1 AND name NOT LIKE 'M%'
THEN salary
WHEN MOD ( employee_id, 2 ) = 0 OR name LIKE 'M%'
THEN 0
END
) AS bonus
FROM
Employees
ORDER BY
employee_id
评论区