leetcode 196. Delete Duplicate Emails

来源：互联网发布：校园招聘java面试题库编辑：程序博客网时间：2024/06/05 17:31

题目：196. Delete Duplicate Emails
1。建表

create table Person(    Id int not null auto_increment,    Email varchar(30) default null,    primary key (Id))insert into Person(Email) values ('john@example.com');insert into Person(Email) values ('john@example.com');insert into Person(Email) values ('bob@example.com');insert into Person(Email) values ('tom@example.com');insert into Person(Email) values ('tom@example.com');insert into Person(Email) values ('tom@example.com');select * from Person;

2。解答：

DELETE FROM PersonWHERE Id NOT IN (  SELECT * FROM (    SELECT Min(Id)    FROM Person    GROUP BY Email  ) tmp)delete from Personwhere  Id not in (    select Id from     (select min(Id) as Id      from Person      group by Email    ) tmp);delete from Person where Id not in (select Id from (    select min(Id) as Id from Person    group by Email)as tmp)DELETE p1FROM Person p1, Person p2WHERE p1.Email = p2.Email ANDp1.Id > p2.Id;

阅读全文

1 0