非常有效删除重复行

 

EXP:

select line,value,k0 from kk

1 10 A
1 10 A
1 10 A
3 31 B
3 31 B
1 10 A
1 10 A
1 10 A
3 31 B
3 31 B

解决快速的语句:

delete from kk a where a.rowid NOT in(select max(b.rowid) from kk b

where a.line=b.line and a.value=b.value and a.k0=b.k0)

----------------------------------------------------------------

如果表的列数少的，这个语句就好用！

利用分析函数：

delete from kk where rowid in(
select row_id from (
select rowid row_id,row_number() over(partition by line,k0,value order by rowid ) rn from kk
) where rn<>1)

RESULT:

1 1 10 A
2 2 40 B
3 3 31 B
4 1 22 A
5 1 31 B