欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

LeetCode——Delete Duplicate Emails(巧用mysql临时表)

程序员文章站 2022-05-18 21:26:05
此题有两个解法: 我初步尝试用以下 解决问题(要删除的记录 肯定大于相同内容的 ): 但是无法通过,究其原因是在 语句中, 与`DELETE`操作不能同时存在. 答案一 因此尝试新的解法,直接使用删除语句,结果如下所示: 此答案通过测试,但是效率较低. 答案二 后续思考中发现,可以使用临时表解决 与 ......
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语句中,selectdelete操作不能同时存在.

答案一

因此尝试新的解法,直接使用删除语句,结果如下所示:

delete p1 from person p1, person p2 where p1.id > p2.id and p1.email = p2.email;

此答案通过测试,但是效率较低.

答案二

后续思考中发现,可以使用临时表解决selectdelete同时存在的问题,答案如下所示:

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