oracle 查询 删除重复的电子邮箱后的表数据 重复邮箱里只保留id最小的

1，写个建表语句

​create table personemail(
       id varchar(10) not null primary key,
       email varchar(20) not null
);
​ 顺便写个删表语句
truncate table personemail;

2，表中插入数据

insert into personemail values(1,'john@example.com');
insert into personemail values(2,'bob@example.com');
insert into personemail values(3,'john@example.com');
insert into personemail values(4,'john@example.com');
insert into personemail values(5,'bob@example.com');
insert into personemail values(6,'smith@example.com');

 

3，找出相同数据

select e1.id e1id,e1.email e1email,e2.id e2id,e2.email e2email from personemail e1,personemail e2 
where e1.email = e2