(3).SQL>select * from cz a where rowid=(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20); C1 C10 C20 ---------- ---------- --- 1 2 dsf 2 3 che 3 4 dff
SQL>delete cz where (c1,c10,c20) in (select c1,c10, c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in (select min(rowid) from cz group by c1,c10,c20 having count(*)>1);
SQL>delete cz where rowid not in (select min(rowid) from cz group by c1,c10,c20);
(2).适用于有少量重复记录的情况(注意,对于有大量重复记录的情况,用以下语句效率会很低):
SQL>delete from cz a where a.rowid!=(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL>delete from cz a where a.rowid<(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL>delete from cz a where rowid <(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
(3).适用于有少量重复记录的情况(临时表法):
SQL>create table test as select distinct * from cz; (建一个临时表test用来存放重复的记录)
SQL>truncate table cz; (清空cz表的数据,但保留cz表的结构)
SQL>insert into cz select * from test; (再将临时表test里的内容反插回来)