oracle表字段去重

单字段去重

查看重复数据

select * from testrepeat t where t.id in (select id from testrepeat group by id having count(id) > 1);

删除重复记录，保留rowid最小的行

delete from testrepeat t
 where t.id in (select id from testrepeat group by id having count(id) > 1)
   and rowid not in
       (select min(rowid) from testrepeat group by id having count(*) > 1)

多字段去重

查看重复数据

select * from testrepeat t where (t.id,t.phone) in (select id,phone from testrepeat group by id,phone having count(*) > 1);

删除重复记录，保留rowid最小的行

delete from testrepeat t
 where  (t.id,t.phone) in (select id,phone from  testrepeat group by id,phone having count(*) > 1)
   and rowid not in
       (select min(rowid) from testrepeat group by id,phone having count(*) > 1)