Oracle SQL查找删除重复记录有两种方式一种是建立一个临时表,就不做说明了.
另一种是用Oracle的rowid来删除:
create table student (
name varchar(12), --姓名
sex varchar2(2) --性别
)
表已建立.
SQL> insert into student values('张三','01');
SQL> insert into student values('李四','01');
SQL> insert into student values('王五','02');
SQL> insert into student select * from student ;
插入4个记录.
SQL> commit;
完全提交.
SQL> select rowid,name ,sex from student ;
查询到6记录.
查出重复记录
select rowid,name ,sex from student
where student.rowid!=(select max(rowid) from student t where student.name =t.name and student.sex =t.sex );
删除重复记录
delete from student where student.rowid!=(select max(rowid) from student t where student.name =t.name and student.sex =t.sex );
删除3个记录.
select rowid,name ,sex from student
完成!