oracle去除重复数据

1、删除所有重复的行

delete from dis_zj_dz_back t
 where t.rowid in (select rid
                     from (select t1.rowid rid,
                                  count(1) over(partition by t1.JOB_FLOW) rn
                             from dis_zj_dz_back t1) t2
                    where t2.rn > 1);

删除并备份

create table dis_zj_dz_back2 as 
select * from dis_zj_dz_back t
 where t.rowid not in (select rid
                     from (select t1.rowid rid,
                                  count(1) over(partition by t1.JOB_FLOW) rn
                             from dis_zj_dz_back t1) t2
                    where t2.rn > 1);

2、删除重复数据并保留一条

delete from dis_zj_dz_back t
 where t.rowid in (select rid
                     from (select t1.rowid rid,
                                  row_number() over(partition by t1.job_flow order by create_time asc) rn
                             from dis_zj_dz_back t1) t2
                    where t2.rn > 1);

删除并备份

create table dis_zj_dz_back2 as 
select * from dis_zj_dz_back t
 where t.rowid not in (select rid
                     from (select t1.rowid rid,
                                  row_number() over(partition by t1.job_flow order by create_time asc) rn
                             from dis_zj_dz_back t1) t2
                    where t2.rn > 1);

 

参考：https://blog.csdn.net/nayi_224/article/details/82020913