删除重复行的办法

delete duplicate rows with Oracle SQL

 

set feedback off

create table T (col1 varchar2(5), col2 varchar2(2));


insert into T values ('a','a');
insert into T values ('a','a');

insert into T values ('a','z');
insert into T values ('a','z');

insert into T values ('a',null
);
insert into T values ('a',null
);

insert into T values ('m','q');
insert into T values ('m','q');

insert into T values ('m',null
);

insert into T values ('s','c');
insert into T values ('s','c');
insert into T values ('s','c');

insert into T values ('s','x');

insert into T values ('v','u');


delete from T t1
where t1.rowid
 > 
             ( select min(t2.rowID) from T t2
               where t1.col1 = t2.col1
               and t1.col2 = t2.col2);


select * from t;

drop table t;