数据库教程:LeetCode——Delete Duplicate Emails（巧用mysql临时表）

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语句比较清晰明了.

需要了解更多数据库技术:LeetCode——Delete Duplicate Emails（巧用mysql临时表），都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!