【多解法】力扣196. 删除重复的电子邮箱

今天刷了一道力扣的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	email
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	email	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 -