LeetCode：Delete Duplicate Emails - 删除重复的邮箱数据

1、题目名称

Delete Duplicate Emails（删除重复的邮箱数据）

2、题目地址

https://leetcode.com/problems/delete-duplicate-emails/

3、题目内容

写一个SQL删除表Person中所有的重复数据，对于重复的数据只保留Id最小的数据

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

例如，在执行完该SQL后，表中剩余数据如下：

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

4、初始化数据库脚本

在MySQL数据库中建立一个名为LEETCODE的数据库，用MySQL命令行中的source命令执行下面脚本：

-- 执行脚本前必须建立名为LEETCODE的DATABASE
USE LEETCODE;
  
DROP TABLE IF EXISTS Person;
CREATE TABLE Person (
  Id INT NOT NULL PRIMARY KEY,
  Email VARCHAR(50)
);

-- INSERT INTO Person (Id, Email) VALUES (1, 'john@example.com');
-- INSERT INTO Person (Id, Email) VALUES (2, 'bob@example.com');
-- INSERT INTO Person (Id, Email) VALUES (3, 'john@example.com');
INSERT INTO Person (Id, Email) VALUES (1, 'tsybius@example.com');
INSERT INTO Person (Id, Email) VALUES (2, 'tsybius@example.com');

5、解题SQL1

下面是一个比较简单的写法

DELETE P2 FROM Person AS P1, Person AS P2
WHERE  P1.Email = P2.Email AND
       P1.Id < P2.Id;

6、解题SQL2

另一种写法是使用 NOT IN 来写，但下面的写法是有问题的，放在MySQL中执行时会报错

DELETE FROM Person
WHERE  Id NOT IN (SELECT MIN(ID) MIN_ID
                  FROM   Person
                  GROUP  BY Email)

报错信息如下：

ERROR 1093 (HY000): You can't specify target table 'Person' for update in FROM clause

这是因为SELECT语句不能直接出现在UPDATE语句（DELETE语句也是一样）的WHERE子句中，把SQL改成下面这样就可以AC了：

DELETE FROM Person
WHERE  Id NOT IN (SELECT TMP.MIN_ID
                  FROM   (SELECT MIN(ID) MIN_ID
                          FROM   Person
                          GROUP  BY Email) TMP);

END

转载于:https://my.oschina.net/Tsybius2014/blog/644854