oracle删除多余重复数据,oracle 删除重复多余的数据

创建测试用的数据

drop table student;

create table student(sno number(10), sname varchar2(10),sage int);

insert into student  values(1,'aa',21);

insert into student  values(1,'aa',21);

insert into student  values(2,'bb',22);

insert into student  values(3,'cc',23);

insert into student  values(3,'cc',23);

insert into student  values(3,'cc',23);

目的是是表student 出去重复多余的行。

方法一： delete from student where rowid not in (select min(rowid) from student group by sno );

rowid与分组后的rowid 最小值比较。

方法二：delete from student where rowid in (select a.rowid from student a, student b

where a.sno=b.sno and a.rowid > b.rowid);

先 自连之后比较两个表的rowid  。

方法三： delete from student a where a.rowid >(select min(b.rowid) from student b where a.sno=b.sno);

两表之间直接比较rowid,