[SQL]LeetCode196. 删除重复的电子邮箱 | Delete Duplicate Emails
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/9744408.html
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
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 | +----+------------------+
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
517ms
1 DELETE 2 FROM person 3 WHERE id NOT IN (SELECT t.id 4 FROM ( 5 SELECT min(id) AS id 6 FROM Person 7 GROUP BY email 8 ) t);
500ms
1 # Write your MySQL query statement below 2 DELETE 3 FROM Person 4 WHERE Id IN ( 5 SELECT Id 6 FROM ( 7 SELECT 8 Id, Email, (Email = @prev) AS dup, @prev := Email 9 FROM ( 10 SELECT Email, Id 11 FROM Person 12 ORDER BY 1, 2 13 ) AS sorted, 14 (SELECT @prev := NULL) AS init 15 ) AS t 16 WHERE dup IS TRUE 17 )
505ms
1 # Write your MySQL query statement below 2 delete from Person where Id not in(select t.Id from (select min(Id) id from Person b group by Email) t)
