LeetCode DB : Delete Duplicate Emails-优快云博客

本文介绍如何使用SQL查询从名为Person的表中删除所有重复的电子邮件条目，仅保留基于最小ID的唯一电子邮件。提供了两种解决方案：一是使用带有where条件的嵌套查询；二是通过不在子查询中选择最小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 its smallest 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  |
+----+------------------+

考察删除中运用where条件

# Write your MySQL query statement below
delete from Person where id in  (select * from (select A.id from Person as A, Person as B where A.id>B.id and A.Email = B.Email) X);

另外一种方法，也是用了嵌套查询

delete from Person where Id not in 
(select min_id from (select min(Id) as min_id from Person group by Email) as Cid) ;

转载于:https://www.cnblogs.com/lailailai/p/4700546.html