SQL 196. 删除重复的电子邮箱

题目 ： https://leetcode.cn/problems/delete-duplicate-emails/

数据

drop table Person;

Create table If Not Exists Person (Id int, Email varchar(255));

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');

需求

删除所有重复的电子邮件，只保留一个 id 最小的唯一电子邮件

查询结果 :

| id | email            |
| 1  | john@example.com |
| 2  | bob@example.com  |

解决

with t1 as (
  select min(id) min_id 
  from Person t
  group by email
)
delete t2 from Person t2 where id not in (
  select min_id as id
  from t1
)