最快的方法是用row_number() over (partition by 关键字 order by ...) 删除序号大于1的,这个方法经过实践后,速度最快,而且代码容易理解,完整代码是: delete from 表 where ROWID in ( select RID from (select ROWID as RID, row_number() over (partition by 关键字 order by rowid) as rn from 表) a where a.rn>1 ) |
eg:
delete from t_identify_relevance_temp where ROWID in (
select RID from (select ROWID as RID, row_number() over (partition by prod_inst_num,identify_id,cust_id order by rowid) as rn from t_identify_relevance_temp) a
where a.rn>1
)