Delete Duplicate Emails

kgduu

于 2015-05-27 11:59:46 发布

阅读量814

点赞数

CC 4.0 BY-SA版权

分类专栏： OJ

本文链接：https://blog.youkuaiyun.com/xiexingshishu/article/details/46044887

OJ 专栏收录该内容

986 篇文章

订阅专栏

本文介绍如何使用SQL查询删除Person表中重复的邮箱记录，只保留每个邮箱对应的ID最小的一条。通过内联查询与条件筛选实现数据去重。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on itssmallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

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

题意：删除email重复的记录，但是要保留id最小的一个

用时1285ms

delete p1 from Person as p1 inner join Person as p2 on p1.id > p2.id and p1.email = p2.email;