delete from tbname where rowid not in (select min(rowid) from tb group by col1,col2..);
not in的效率很忙地 delete from tbname where rowid <(select max(rowid) from tb group by col1,col2..);
可以利用这样的命令来删除表内重复记录: 不过,当表比较大(例如50万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法 delete from table_name a where rowid< (select max(rowid) from table_name where column1=a.column1 and column2=a.column2 and colum3=a.colum3 and ...);
还可以这样: delete from tablename a where rowid >(select min(rowid) from table b where condition ...);
1. delete from t where rowid not in ( select max(rowid) from t group by col1,col2 )2. delete from t where rowid in ( select row_id from ( select rowid row_id,row_number() over (partition by col1,col2 order by rowid ) rn ) where rn <> 1 )3. 使用建表的方式也很不错 4. 可以考虑使用exceptions into clause
where rowid not in (select min(rowid) from tb group by col1,col2..);
delete from tbname
where rowid <(select max(rowid) from tb group by col1,col2..);
不过,当表比较大(例如50万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法
delete from table_name a
where rowid< (select max(rowid) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);
delete from tablename a
where rowid >(select min(rowid) from table b where condition ...);
select max(rowid) from t group by col1,col2
)2. delete from t where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by col1,col2 order by rowid ) rn
)
where rn <> 1
)3. 使用建表的方式也很不错
4. 可以考虑使用exceptions into clause