LeetCode196. Delete Duplicate Emails

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 |
+—-+——————+
Subscribe to see which companies asked this question

问题：用SQL语句将表（Person）中重复的Email字段删除（保留Id值最小的那一条记录）。

思路：
首先找到那些重复的Email字段(选择出按Email分组后Email条数大于一的那些Email字段值)：

select Email from Person group by Email having count(Email)>1;

其次找出重复的Email字段中每组中的最小Id值：

select min(Id) from Person group by Email having count(Email)>1;

删除重复的Email字段删除（保留Id值最小的那一条记录）。最后的语句就是

delete from Person where Email in (select Email from Person group by Email having count(Email)>1) and Id not in (select min(Id) from Person group by Email having count(Email)>1);

好像大功告成的样子，结果：
Runtime Error Message: You can’t specify target table ‘Person’ for update in FROM clause

就是说不能先select出同一表中的某些值，再update这个表(在同一语句中)。

解决方法：

将select出的结果再通过中间表select一遍同时起一个别名，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。

改为：

delete from Person where Email in (select * from (select Email from Person group by Email having count(Email)>1) as p) and Id not in  (select * from (select min(Id) from Person group by Email having count(Email)>1) as d) ;

这样就可以了。

本着科学严谨的态度想了想这道题的思路，发现只要删除掉分完组之后，id值不是最小Id值得那些记录就行了。

delete from Person where Id not in (select * from (select min(Id) from Person group by Email) as d) ;