1873. 计算特殊奖金
select employee_id,
case
when employee_id in (select employee_id from Employees where name not like "M%" and employee_id%2=1) then salary
else 0
end as bonus
from Employees
order by employee_id;
练习 case when...then... when...then... else... end 判断语句,其结果存于end后紧跟的字段中。
196. 删除重复的电子邮箱
DELETE p1 from Person p1 join Person p2 on p1.email=p2.email and p1.id>p2.id;
题目要求使用delete语句,可以先用select做。
输入
Person 表:
+---+-------+
| id | email |
+---+-------+
| 1 | A |
| 2 | B |
| 3 | A |
+---+-------+
先通过select查看所有结果
select * from Person p1 join Person p2 on p1.email=p2.email;
结果为:(从左往右字段依次为:p1.id,p1.email,p2.id,p2.email)
1 A 1 A
3 A 1 A
2 B 2 B
1 A 3 A
3 A 3 A
按照题目要求,想办法删除3 A即可,
若是delete p1(只看前两个字段)则添加条件筛选出3 A 1 A或者3 A 3 A,
若是delete p2(只看后两个字段)则添加条件筛选出1 A 3 A或者3 A 3 A,
如果要筛选出3 A 3 A,只能用p1.id=p2.id,此时1 A 1 A和2 B 2 B也会被筛选出,不符合题意,
因此对于delete p1只能用p1.id>p2.id,对于delete p1只能用p1.id<p2.id。
1484. 按日期分组销售产品
select
sell_date,
count(distinct product) num_sold,
group_concat(distinct product order by product asc separator ',') products
from
Activities
group by
sell_date;
group_concat:将组内的数据连接
distinct:与group_concat搭配使用时默认按字段升序排列,若要更改排序通过order by修改
order by product asc separator ',':distinct默认升序,group_concat默认‘,’连接,因此这段sql可以省略
1148. 文章浏览 I
group by 效率要高于 distinct
#使用group by
select author_id id
from Views
where author_id = viewer_id
group by author_id
order by id;
#使用distinct
select distinct author_id id
from Views
where author_id = viewer_id
order by id;