Leetcode_10 天 SQL 入门_day2

继续，继续。

1873. Calculate Special Bonus

Analysis

挑出满足两个条件的数据：

1. name 不是以 M 开头
2. id 是奇数

Code

select employee_id, 
if(employee_id % 2 = 0 or name like 'M%', 0, salary) as bonus
from Employees

627. Swap Salary

Analysis

sex 为 m 就改为 f，为 f 就改为 m。

Code

method 1

update Salary set sex = if(sex = 'm', 'f', 'm')

method 2

也可以用 CASE WHEN 来完成。

update Salary
set
  sex = CASE sex
    WHEN 'm' THEN 'f'
    ELSE 'm'
  END;

196. Delete Duplicate Emails

Analysis

删除重复元素即可。

Code

method 1

delete p1.*
from Person p1, Person p2
where p1.Email = p2.Email and p1.id > p2.id

method 2

如果这个题不限制 select 的话，其实还有另外一种方法：

delete from Person
where id not in (select dt.id from (select min(id) as id from Person group by email) dt)

Summary

今天的内容主要是删除和修改的内容，不是特别难，主要还是熟悉基本用法。

---