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