Oracle数据库中去除重复数据

Rehoni / 2020-06-29

全表重复

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
    );

提取更新时间最近的一条记录

select * from
(select t.EMPLOYEE_ID,
       EMPLOYEE_NAME,
       a.ORG_ID,
       b.ORG_NAME,
       t.SHORT_SPELL,
       t.FULL_SPELL,JOBSTATUS,EMPLOYEE_STATUS,NATIONALITY,SEX,EDUCATION,MOBILE_PHONE,FIX_PHONE,EMAIL,t.UPDATE_TIME,
       row_number() over (partition by EMPLOYEE_NAME order by t.UPDATE_TIME) rn
from SJHL_ZH.NZWOT_TOP_EMPLOYEE t
    left join NZWOT_TOP_USER a on a.EMPLOYEE_ID = t.EMPLOYEE_ID
    inner join (select org_id, org_name, parent_org_id, org_type, name_full_path, update_time from sjhl_zh.nzwot_top_organization
         where state = 1 and regexp_like(name_full_path, '(变电管理所|输电管理所|试验研究所)')
         start with parent_org_id = ( select org_id from sjhl_zh.nzwot_top_organization
         where name_full_path = '中国南方电网有限责任公司/广东电网有限责任公司/珠海供电局'
         and state = 1 and org_level = 3 ) connect by prior org_id = parent_org_id
         union
         select org_id, org_name, null, org_type, name_full_path, update_time from sjhl_zh.nzwot_top_organization
         where name_full_path = '中国南方电网有限责任公司/广东电网有限责任公司/珠海供电局' and state = 1 and org_level = 3) b on b.ORG_ID = a.ORG_ID) tb
         where tb.rn = 1;