题目: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 Person
WHERE Id NOT IN (
SELECT * FROM (
SELECT Min(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
) tmp
);
delete from Person
where Id not in (
select Id from (
select min(Id) as Id from Person
group by Email
)as tmp
)
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND
p1.Id > p2.Id;