Delete Duplicate Emails-K6K4.com

原题： https://leetcode.com/problems/delete-duplicate-emails/description/

题意： 编写SQL删除Person表中所有的重复email条目，只保留Id最小的唯一email记录。

例子：

给定Person表：
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

执行查询语句之后，Person表格包含信息如下：
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

标签： email、john、person、emails、duplicate、面试

猜你感兴趣的圈子：

LeetCode交流圈

Bingo

2017-08-17 18:37:09 1楼#1层

解法一：

DELETE FROM Person WHERE Id NOT IN
(SELECT Id FROM (SELECT MIN(Id) Id FROM Person GROUP BY Email) p);

Bingo

2017-08-17 18:37:22 2楼#1层

解法二：

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

Bingo

2017-08-17 18:37:35 3楼#1层

解法三：

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

取消

回复

推荐圈子

LeetCode交流圈