2排序 & 修改

1.力扣1873.计算特殊奖金

 case when：

【SQL】SQL中Case When的用法 - HDWK - 博客园 (cnblogs.com)

 if ：

 语法结构：IF(expr,v1,v2)

 其中：表达式 expr 为条件式，当 expr 为真是返回 v1 的值，否则返回 v2

 

i.case when

# Write your MySQL query statement below
SELECT
   employee_id,
   CASE WHEN employee_id % 2 = 1 AND name not Like 'M%' THEN salary ELSE 0 END as bonus
FROM
   Employees
ORDER BY employee_id;

ii.if

# Write your MySQL query statement below
SELECT
   employee_id,
   if(employee_id % 2 = 1 AND name not Like 'M%',salary,0) as bonus
FROM
   Employees
ORDER BY employee_id;

not Like 'M%' 等价于 left(name,1) != 'M'

2.力扣627.变更性别

i.if

# Write your MySQL query statement below
UPDATE Salary SET sex = if(sex = 'm','f','m');

ii.case when

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

3.力扣196.删除重复的电子邮箱

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
DELETE P1
FROM Person P1,Person P2
WHERE 
    P1.email = P2.email AND P1.id > P2.id;