ORACLE删除表中某一列重复的行

Demo

 创建表

--表名：cux_repeat

create table  cux_repeat
(
id number(10),
firstname varchar2(50 char),
lastname varchar2(50 char)
);

 

插入数据

--插入数据
insert into cux_repeat values (1, 'F1', 'L1');
insert into cux_repeat values (2, 'F2', 'L2');
insert into cux_repeat values (3, 'F3', 'L3');
insert into cux_repeat values (2, 'F2', 'L2');
insert into cux_repeat values (3, 'F3', 'L3');
insert into cux_repeat values (3, 'F3', 'L3');
insert into cux_repeat values (4, 'F4', 'L4');
insert into cux_repeat values (3, 'F4', 'L4');

思路：

1.先将所有行分组后取得最大ROWID。其结果就包含不重复的行，及重复行的最后一笔的ROWID

   select max(a.rowid) from cux_repeat a group by a.firstname, a.lastname

2.将表中除此以外的行删掉就可以了

 

方法一：删除姓名一样的重复行数据

--姓名一样的删除
delete from cux_repeat c
 where c.rowid not in (select max(a.rowid)
                         from cux_repeat a
                        group by a.firstname, a.lastname);

 

方法二：(不建议使用)

delete from cux_repeat cp
 where (cp.firstname, cp.lastname) in
       (select x.firstname, x.lastname
          from cux_repeat x
         group by x.firstname, x.lastname
        having count(1) > 1)
   and cp.rowid not in (select min(a.rowid)
                          from cux_repeat a
                         group by a.firstname, a.lastname
                        having count(1) > 1);