Leetcode 196 Delete Duplicate Emails

删除Person表中重复邮箱并保留最小ID记录

weixin_30298497

于 2015-06-26 10:35:00 发布

阅读量98

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lilixu/p/4601731.html

本文提供了一段SQL查询代码，用于在Person表中删除重复的邮箱条目，仅保留每个邮箱对应的最小ID记录。通过比较表中各条目的邮箱和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 a from Person a, Person b
where a.Email = b.Email and a.Id > b.Id

转载于:https://www.cnblogs.com/lilixu/p/4601731.html