Oracle——查找和去除重复数据

Rehoni / 2020-06-29

查找重复（根据字段）

查询两个字段是否是一对一的关系：count_a != count_b时，不是一对一

 select count(distinct a) count_a,count(distinct b) count_b from table t;

假设count_a>count_b，那么查询重复的a的记录：

 select a,count(a) count_a
 from table t
 group by t.a
 having count(a)>1;

那么查询对应的表的记录为：

 select *
 from table
 where a in (
         select a,count(a) count_a
         from table t
         group by t.a
         having count(a)>1);

去除重复

全表重复

 create table maindevicetemp as (
     select distinct *
     from ELECTRANS.GEA_DM_RP_MAIN_DEVICE
 );
 
 truncate table ELECTRANS.GEA_DM_RP_MAIN_DEVICE;
 
 insert into ELECTRANS.GEA_DM_RP_MAIN_DEVICE
 select * from maindevicetemp;
 
 drop table maindevicetemp;

部分重复

直接删除查出来的代码，1. 可以通过把查出来的表放入临时表的操作来提高效率；2. 可以通过选择隐藏字段rowid，取最大的rowid（而不用group by … having count(*) >1 的操作）为最新的记录。

 create table geamaindevice as
 select a.OBJECT_ID, max(a.ROWID) dataid
 from ELECTRANS.GEA_DM_RP_MAIN_DEVICE a
 group by a.OBJECT_ID;
 
 select * from geamaindevice;
 
 delete from ELECTRANS.GEA_DM_RP_MAIN_DEVICE a
 where a.ROWID <> (
     select b.dataid from geamaindevice b
     where a.OBJECT_ID = b.OBJECT_ID
     );