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 PersonWHERE 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
语句比较清晰明了.
PS:
如果您觉得我的文章对您有帮助,请关注我的微信公众号,谢谢!
联系客服