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);//此子句查出的是要留下的重复记录中的其中一条
//注:同样,rowid等同于主键,所以也可用其他主键代替,
SQL语句二:
delete cz where rowid not in(select min(rowid)
from cz group by c1,c10,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);
方法三:适用于有少量重复记录的情况(临时表法)
{没有主键的表如何消重复记录}:
create table test1 as select distinct *
from test;
drop table test;
rename test1 to test;--重命名表名
{含有主键的表如何消重复记录}:
create table test1 as select distinct mk
from test;--mk为除去主键的其他字段。
alter table test1 add(id number(3));--添加ID字段(待做主键)
update test1 set id=(select max(id)
from test where mk=test1.mk);--为主键字段填值(选取原表相应重复记录中的最大主键值)