196. Delete Duplicate Emails - 删除重复的电子邮箱 ＜easy＞

本文主要是介绍196. Delete Duplicate Emails - 删除重复的电子邮箱 ＜easy＞，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

提示：

执行 SQL 之后，输出是整个 Person 表。
使用 delete 语句。

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
Note:Your output is the whole Person table after executing your sql. Use delete statement.

思路：删除掉出现多次的邮箱记录，id not in (id ~ count(Email)>1) , 并且保证只有一个邮箱的不被删除

delete from Person 
where id not in (select id from (select min(Id) id from Person group by Email having count(Email) > 1) a
) and Email not in (select Email from (select Email from Person group by Email having count(Email) = 1) b
)

更简单的想法是只保留第一次出现的邮箱：

DELETE from Person 
Where Id not in (Select Id From(Select MIN(Id) as idFrom Person Group by Email) t
)

 

这篇关于196. Delete Duplicate Emails - 删除重复的电子邮箱 ＜easy＞的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---