a情况，删除表中多余的重复记录，重复记录是根据单个字段（businessid）来判断，只留有rowid最小的记录

from cfa
insert into cfa values (1,'Albert','SCB','2011-11-11');

WHERE t.ROWID > (SELECT MIN(X.ROWID)

b情况，删除表中多余的重复记录（多个字段），只留有rowid最小的记录

select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1);

c情况，这种情况就比较简单，使用临时表方法

复制代码 代码如下:

a.重复一个字段

group by businessid,customer
create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
delete from cfa

复制代码 代码如下:

1、查询表中的重复数据

b.重复多个字段

复制代码 代码如下:

对于a的情况,只有businessid重复

复制代码 代码如下:

方法：group by XX having count(*)>1,rowid,distinct,temporary table,procedure

and rowid not in (select min(rowid)

truncate table cfa;--如果是生产最好对该表backup

复制代码 代码如下:

insert into cfa values (1,'Alex','ICBC','2011-11-14');

复制代码 代码如下:

复制代码 代码如下:

或者，使用下面更简单高效的语句

也可以只保留rowid不是最小记录，需要把代码中的min改为max这里不再赘述。

或者，使用下面更简单高效的语句

(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);
select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);

2、删除表中的重复数据

delete from cfa

这时候如果临时表中有重复数据，无论是主键字段businessid有重复，还是一整行有重复都会报出违反唯一主键约束错误。

from cfa
insert into cfa values (1,'Albert','CTBK','2011-11-15');

对于c的情况，重复一整行

from cfa

如果是b的情况，businessid 和name同时存在重复
and x.customer = t.customer);

复制代码 代码如下:

group by businessid,customer

创建测试表：

group by businessid
insert into cfa values (1,'Albert','SCB','2011-11-11');

复制代码 代码如下:

WHERE X.businessid = t.businessid
create table cfabak as select distinct * from cfa;

---------------以下为重复数据----------------------------------------------
group by businessid
having count(*) > 1);

参考b的方法：

having count(businessid) > 1)

Insert into cfa select * from cfabak;

DELETE FROM cfa t

c.重复一整行

commit;
select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);
DELETE FROM cfa t
insert into cfa values (2,'Andy','DB','2011-11-12');

where (businessid,customer) in (select businessid,customer
having count(businessid) > 1);

WHERE t.ROWID >
where businessid in (select businessid

FROM cfa X
insert into cfa values (3,'Allen','HSBC','2011-11-13');

from cfa

and rowid not in (select min(rowid)
having count(*) > 1)