今天刷了一道力扣的SQL题,总结一下几种解题方法和大家分享一下:
- 采用聚合函数min()
- 使用开窗函数rank()和row_number()
- 采用最简单的where条件
一、题目介绍
196. 删除重复的电子邮箱【点击蓝色字体直达】
二、解题方法
题目要求要使用delete语句来做(如果没有该限制,可以使用的方法更多~~)
2.1 delete语句
那就按照题目要求来,首先先简单介绍一下delete语法。
我把按照删除表的对象分为了三类:同表删除和异表删除,异表删除根据表的数量分为单表删除和多表删除。
怎么理解呢,去掉把【delete】改为【select *】看看联结完的表和要删除的表是不是同一个表,是就是同表,不是就是异表。
delete语法如下:
- 同表删除:delete from table where condition
- 异表删除:
- 单表删除:delete table1 from table1,table2 where table1.column=table2.column and condition
- 多表删除:delete table1, table2 from table1,table2 where table1.column=table2.column and condition
注意:delete 是要删除后面筛选出来的内容
2.2 开始解题
2.2.1 采用聚合函数min()
解题思路:取出要保留的值,然后反向选择,删掉其他的记录。
具体操作:根据email分组,获取到id最小的那一条记录(代码一),然后通过代码一提取到id(代码二),最终反向选择要删除的内容。
# 代码一
select Email,min(Id) as min_id
from Person
group by Email
# 代码二
select p.min_id
from(
select Email,min(Id) as min_id
from Person
group by Email
)p
# 最终代码
delete from Person
where Id not in(
select min_id from(
select Email,min(Id) as min_id
from Person group by Email
)p1
)
2.2.2 采用开窗函数
解题思路:给email分组,然后按照id进行升序排序,取出大于1的(也可以降序排序,选出最大的,反向删除),删掉即可。
具体操作:先用开窗函数rank()/row_number()对email进行排序(把email放在partition by后面),对id进行升序排序(把id放在order by后面),然后对排序结果进行筛选,注意此处是删除出大于1的删掉。(也就是说等于1的是需要保留的)
- rank()
delete from Person
where id in(
select id from
(select id, rank() over (partition by Email order by Id) rn
from Person
) p1
where rn>1
)
- row_number()
delete from Person
where Id in(
select Id
from(
select Id, row_number() over(partition by Email order by Id) rn
from Person
) p1
where rn>1
)
可能有小伙伴纳闷了,这两个函数出了拼写是不是一模一样?
针对该题,确实是,二者没有什么区别,因为排序的列没有重复。如果排序的列重复了,二者就会有细微不同。
假设有这么一个表table1:
可以采用with as来创建一个临时表
with table1 as(
select 11 as "no", "123@example.com" as "email" union all
select 11 as "no", "123@example.com" as "email" union all
select 22 as "no", "456@example.com" as "email" union all
select 33 as "no", "456@example.com" as "email"
)
no | |
11 | 123@example.com |
11 | 123@example.com |
22 | 456@example.com |
33 | 456@example.com |
使用以下查询语句之后得到的结果如下:
select no,email
,rank() over(partition by email order by "no") "rank()"
,row_number() over(partition by email order by "no") "row_number()"
from table1
no | rank() | row_number() | |
11 | 123@example.com | 1 | 1 |
11 | 123@example.com | 1 | 2 |
22 | 456@example.com | 1 | 1 |
33 | 456@example.com | 2 | 2 |
此时就可以很明显的看到,当两条记录相同时,rank()和row_number()的计算结果是不同的。
2.2.3 采用最简单的where条件
最后,来看看最简单粗暴的解法。该法也是官方推荐的解法【点击蓝色字体直达】。
delete p1 from person p1,person p2
where p1.email=p2.email and p1.id > p2.id
解题思路:自联结表,然后取出id较大的进行删除。
具体操作:
将此表与它自身在电子邮箱列中连接起来。
SELECT p1.*
FROM Person p1, Person p2
WHERE p1.Email = p2.Email
;
然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。
SELECT p1.*
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id
;
至此,我们已经得到了要删除的记录,将该select语句更改为 DELETE。
DELETE p1 FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id
有什么疑问和意见,欢迎留言~~ 想了解更多的题目,也可以通过上面的蓝色进入~~
- End -