leetcode【sql】 Delete Duplicate Emails

最新推荐文章于 2024-07-03 07:04:23 发布

转载最新推荐文章于 2024-07-03 07:04:23 发布 · 58 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/LUO77/p/4950243.html

本文介绍了一个SQL查询案例，用于从名为Person的表中删除所有重复的电子邮件条目，仅保留基于最小ID的唯一电子邮件。通过使用两个表的别名进行内连接操作实现，最终保留具有最小ID的记录。

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  |
+----+------------------+

DELETE p1 FROM Person p1 INNER JOIN Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

这里p1和p2都是Person的别名。inner join等于join。所以p1 join p2应该是以下的结果：

------+------+-----------------+
|P1.Id| p2.Id|Email            |
+-----+------+-----------------+
| 1  |   1  | john@example.com |
| 2  |   2  | bob@example.com  |
| 3  |   3  | john@example.com |
+----+-------------------------+
然后p1.id>p2.id所以id为3的被删除了，但是因为p1和p2都是person事实上，只有一个id。为了理解，写成了两列。