Delete Duplicate Emails

Delete Duplicate Emails

description:
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.

---

这道题本身难度不大，但是出了一些语法问题，找了很久才找出错误来。因此记录一下。
题意是给你一个Person表，让你删掉邮件重复的并且要求是id号较大的那些元组，保留下来的是无重复邮件的person信息。

这道题我的思路是：带子查询的delete语句，子查询通过两张Person表按email自连接并找出一张id大于另一张表的id的id号，然后delete语句删掉这个id号就行了。

我最开始的代码：

# Write your MySQL query statement below
delete from Person p3
where p3.id = (select p1.id from Person p1 join Person p2 where
              p1.email=p2.email and p1.id>p2.id);

但是这样写SQL报语法错误了，查了半天才知道:MySQL的删除语句不允许给删除的表起别名
于是我把p3这个别名给去了，这样：

# Write your MySQL query statement below
delete from Person
where id = (select p1.id from Person p1 join Person p2 where
           p1.email=p2.email and p1.id>p2.id);

结果又报错：

You can't specify target table 'Person' for update in FROM clause

查了后才知道：MySQL不允许同时查询和删除同一张表，我删除和查询都用了Person表，用子查询的方式包装一下这个表即可避免这个个错误。
于是我这样写了：

# Write your MySQL query statement below
delete from Person
where id = (select id from (select p1.id from Person p1 join Person p2 where
           p1.email=p2.email and p1.id>p2.id));

果然上个错误没出现，但是又报了另外一个错：

Every derived table must have its own alias

然后我又接着查，这个错误说的是每一个派生出来的表必须拥有自己的别名，这个好办，我就给它加了个别名：

# Write your MySQL query statement below
delete from Person
where id = (select id from (select p1.id from Person p1 join Person p2 where
           p1.email=p2.email and p1.id>p2.id) as A);

这样写的Run Code终于AC了，但是当我提交的时候却又RE了，Error Message说的是子查询多于一行，然后我明白了。要把=改成in，这样就行了，这样就能查出在大于最小的那个id的所有id集合，同时一并删掉。
AC_code:

# Write your MySQL query statement below
delete from Person
where id in (select * from (select p1.id from Person p1 join Person p2 where
            p1.email=p2.email and p1.id>p2.id) as a);

谢谢你的观看！