由于这两天公司值班和投产,事情较忙,没来的及继续学习,赶着要周末有时间了,现在来回顾一下
这一天的专项为 排序&修改。一共 3 道题,3 简单
1873.计算特殊奖金
Table Employees
employee_id | name | salary |
---|---|---|
2 | Meir | 3000 |
3 | Michael | 3800 |
7 | Addilyn | 7400 |
8 | Juan | 6100 |
9 | Kannon | 7700 |
写出一个SQL 查询语句,计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头,那么他的奖金是他工资的100%,否则奖金为0。返回的结果集请按照employee_id排序。
select employee_id,
case when employee_id % 2 != 0 and name not like "M%" then salary else 0 end as 'bonus'
from Employees
order by employee_id;
这里很自然的能够想到 case when 语句,因为它是对表中每一条数据进行条件匹配并返回判断值,与题意一致。
当然,也可以在条件判断中使用 if() 语句,更易理解。
627.变更性别
Table Salary
id | name | sex | salary |
---|---|---|---|
1 | A | m | 2500 |
2 | B | f | 5500 |
3 | C | m | 3000 |
4 | D | f | 1500 |
编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ (即,将所有 ‘f’ 变为 ‘m’ ,反之亦然),仅使用 单个 update 语句 ,且不产生中间临时表。(注意,你必须仅使用一条 update 语句,且 不能 使用 select 语句)
update Salary set sex=if(sex = 'm','f','m');
题目要求是直接更新原表,如果只是按照 select 查询不对原表进行数据操作,可以这样写:
select id,name
,case when sex = 'm' then 'f' else 'm' end
,salary
from Salary;
196.删除重复的电子邮箱
Table Person
id | |
---|---|
1 | john@example.com |
2 | bob@example.com |
3 | john@example.com |
4 | mike@example.com |
编写一个 SQL 删除语句来删除所有重复的电子邮件,只保留一个id最小的唯一电子邮件。以任意顺序返回结果表。
对于我的思路是,删除语句肯定是包裹在最外层的,那么我们首先需要找到重复的电子邮箱以及它对应的ID。
(1)第一种方法
在子查询中可以通过分析函数,来获取重复邮箱的最小ID值
select tid from(
select min(id) over(partition by email) as tid ,email from Person) t1
)
然后与 delete 语句嵌套,形成最终的逻辑
delete p.* from Person p where id not in (
select tid from(
select min(id) over(partition by email) as tid ,email from Person) t1
);
(2)第二种方法
在子查询中可以通过分析函数,对重复邮箱进行排序,获得当序号为1是,对于邮箱的ID,即重复邮箱的最小ID。
最后删除的逻辑与(1)相同,整合嵌套即可写成
delete p.* from Person p where id not in (
select id from(
select row_number() over(partition by email order by id) as tid ,id , email from Person
) t1 where t1.tid = 1
);
(3)官方给出的是自连接的解决方法,代码相对来说会简洁许多,但通过自表关联通常情况下会生成笛卡儿积,而这一解决方法添加了 where 条件判断子句,对关联数据进行了筛选,消除可能产生笛卡儿积的隐患,提高关联判断速度。
delete p1.* from
Person p1 left join Person p2
on p1.email = p2.email
where p1.id > p2.id;
–day 2 的例题分享结束
–欢迎大家共同学习,进步!