工作笔记

工作记录

数据重复问题解决方案

问题场景:人员基本库出现人员数据重复 ,除个人编码外其他数据一致,出现两条或多条数据

--首先第一步根据条件分组查询 每条重复数据查询出一条数据添加到备份表
create table a as 
   select T.D401_01, T.D401_02, T.D401_19                  
          from d T                                              
         WHERE T.D401_19 in ('xx','xx')
         and t.d401_37 = 'xx'
         GROUP BY T.D401_01, T.D401_02, T.D401_19       
        having count(t.d401_01) >1  
--根据a表数据查询原表 d 数据添加到备份表b 注:一定要备份原数据  最好备份两份
 create table b as 
select * from d t  where t.d401_19 in ('xx', 'xx')
    and exists( select 1 from a a  where t.d401_01 = a.d401_01
     and t.d401_02 = a.d401_02
     and t.d401_19 = a.d401_19  )
--分组删除重复数据取个人编码最大一条
DELETE FROM b t
 where t.e401_01 not in
       (select max(t1.e401_01) from b t1 group by t1.d401_01, t1.d401_02,t1.d401_19);
--删除d表所有重复
delete from d t  where t.d401_19 in ('xx', 'xx')
    and exists( select 1 from a a  where t.d401_01 = a.d401_01
     and t.d401_02 = a.d401_02
     and t.d401_19 = a.d401_19 )
--恢复数据
INSERT INTO d SELECT *  FROM b

因为个人oracle技术过差,这是前辈教的方法 特此记录!