2.删除重复记录的几种方法:
(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候,用以下语句效率会很高):
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里的内容反插回来)
create index index_ts_top on ts_top_dom(top_dom);
delete ts_top_dom where top_dom in (select top_dom from ts_top_dom group by top_dom having count(*)>1) and rowid not in
(select min(rowid) from ts_top_dom group by top_dom having count(*)>1);
delete ts_top_dom where rowid not in
(select min(rowid) fromts_top_dom group by top_dom );
select distinct TOP_DOM from TOP_DOM
truncate table ts_top_dom;//快速删除表中数据
(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候,用以下语句效率会很高):
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里的内容反插回来)
create index index_ts_top on ts_top_dom(top_dom);
delete ts_top_dom where top_dom in (select top_dom from ts_top_dom group by top_dom having count(*)>1) and rowid not in
(select min(rowid) from ts_top_dom group by top_dom having count(*)>1);
delete ts_top_dom where rowid not in
(select min(rowid) fromts_top_dom group by top_dom );
select distinct TOP_DOM from TOP_DOM
truncate table ts_top_dom;//快速删除表中数据