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 |
+----+------------------+
Note:
Your output is the whole Person table after executing your sql. Use delete statement.此题有两个解法：
我初步尝试用以下sql解决问题(要删除的记录Id肯定大于相同内容的Id):
delete p1 from Person p1, Person p2 where p2.id > p1.id and p2.Email = p1.Email;但是无法通过，究其原因是在sql语句中，SELECT与DELETE操作不能同时存在.
答案一
因此尝试新的解法，直接使用删除语句,结果如下所示:
delete p1 from Person p1, Person p2 where p1.id > p2.id and p1.Email = p2.Email;此答案通过测试，但是效率较低.
答案二
后续思考中发现，可以使用临时表解决SELECT与DELETE同时存在的问题，答案如下所示:
DELETE FROM Person
WHERE Id IN
(
SELECT Id FROM
(SELECT p1.Id as Id FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id) AS TEMP
)此解决方案完美解决问题，且sql语句比较清晰明了.
内容来源于网络如有侵权请私信删除
- 还没有人评论，欢迎说说您的想法！
客服
