SQL架构
表: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id是该表的主键列。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。
以 任意顺序 返回结果表。 (注意: 仅需要写删除语句,将自动对剩余结果进行查询)
查询结果格式如下所示。
示例 1:
输入: Person 表: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ 输出: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ 解释: john@example.com重复两次。我们保留最小的Id = 1。
DELETE
from
Person
where id in
(SELECT#过滤后剩下的email的id
ttt.p2d
from
(SELECT#剔除重复email中 ID编号排第一的
p2.id as p2d,dense_rank() over(partition by p2.email order by p2.id) as dr
from
Person as p2,
(SELECT #选出重复的email
email as e1, count(email) as c1
from
Person
group by
email) as p1
where
p2.email = p1.e1 and p1.c1>1
group by
p2.id) as ttt
where ttt.dr>1 )
DELETE FROM PERSON
WHERE ID
NOT IN
(SELECT * FROM
(
SELECT MIN(ID)
FROM PERSON
GROUP BY EMAIL
) AS P1)