1873 计算特殊奖金

计算特殊奖金

涉及知识点

1. CASE WHEN ... THEN ... ELSE ... END
2. ORDER BY (排序）

# Write your MySQL query statement below
SELECT employee_id, 
CASE WHEN LEFT(name, 1) != 'M' AND MOD(employee_id, 2) = 1 THEN salary ELSE 0 END bonus
FROM employees
ORDER BY employee_id

CASE WHEN

MySQL的case when的语法有两种

1. 简单函数：CASE [col_name] WHEN [value] THEN [result1]... ELSE [default] END
2. 搜索函数：CASE WHEN [expr] THEN [result1] ... ELSE [default] END

简单函数CASE [col_name] WHEN [value] THEN [result1]... ELSE [default] END

WHEN 后面判断的条件是CASE 后面的列。如果需要判断多条件，使用AND连接列名，但是结果有点奇怪。具体可以看 个人的LeetCode题解。

# Write your MySQL query statement below
SELECT 
    employee_id, name, 
    
    CASE name
        WHEN LEFT(name, 1) != 'M' THEN 
            1000
        ELSE 2000
    END salary
FROM employees
ORDER BY employee_id

employee_id	name	salary
2	Meir	1000
3	Michael	1000
7	Addilyn	2000
8	Juan	2000
9	Kannon	2000

搜索函数

# Write your MySQL query statement below 
SELECT 
    employee_id, 
    CASE WHEN 
        LEFT(name, 1) != 'M' AND MOD(employee_id, 2) = 1 THEN salary 
        ELSE 0 
    END bonus 
FROM employees 
ORDER BY employee_id

LEFT(str, length) 返回具有指定长度的字符串的左边部分，length过长时，返回整个字符串，为0或负数时，返回空字符串。

627 变更性别

涉及知识点

1. update
2. CASE WHEN

# Write your MySQL query statement below
UPDATE 
    Salary 
    SET sex = 
        CASE sex WHEN 'm' THEN 'f' ELSE 'm' 
        END

196 删除重复的电子邮箱

删除重复的电子邮箱

涉及知识点

1. DELETE
2. 自连接

DELETE P1.*
FROM Person P1, Person P2
WHERE P1.email = P2.email AND P1.id > P2.id

对于该题，见该处的解释。、

参考资料

1. blog.csdn.net/qq_30038111…
2. www.jianshu.com/p/f09860639…
3. www.yiibai.com/mysql/left-…