LeetCode DB : Delete Duplicate Emails

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) ;
上一篇:JavaScript -- FileSystemObject


下一篇:学习ASP.NET MVC(六)——我的第一个ASP.NET MVC 编辑页面