這時(shí)候如果臨時(shí)表中有重復(fù)數(shù)據(jù)，無(wú)論是主鍵字段businessid有重復(fù)，還是一整行有重復(fù)都會(huì)報(bào)出違反唯一主鍵約束錯(cuò)誤。
方法：group by XX having count(*)>1,rowid,distinct,temporary table,procedure
1、查詢(xún)表中的重復(fù)數(shù)據(jù)
a.重復(fù)一個(gè)字段
b.重復(fù)多個(gè)字段
c.重復(fù)一整行
創(chuàng)建測(cè)試表：
復(fù)制代碼 代碼如下:
create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
insert into cfa values (1,'Albert','SCB','2011-11-11');
insert into cfa values (2,'Andy','DB','2011-11-12');
insert into cfa values (3,'Allen','HSBC','2011-11-13');
---------------以下為重復(fù)數(shù)據(jù)----------------------------------------------
insert into cfa values (1,'Alex','ICBC','2011-11-14');
insert into cfa values (1,'Albert','CTBK','2011-11-15');
insert into cfa values (1,'Albert','SCB','2011-11-11');
對(duì)于a的情況,只有businessid重復(fù)
復(fù)制代碼 代碼如下:
select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);
如果是b的情況，businessid 和name同時(shí)存在重復(fù)
復(fù)制代碼 代碼如下:
select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);
對(duì)于c的情況，重復(fù)一整行
參考b的方法：
復(fù)制代碼 代碼如下:
select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1);
2、刪除表中的重復(fù)數(shù)據(jù)
a情況，刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（businessid）來(lái)判斷，只留有rowid最小的記錄
也可以只保留rowid不是最小記錄，需要把代碼中的min改為max這里不再贅述。
復(fù)制代碼 代碼如下:
delete from cfa
where businessid in (select businessid
from cfa
group by businessid
having count(businessid) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid
having count(businessid) > 1);
或者，使用下面更簡(jiǎn)單高效的語(yǔ)句
復(fù)制代碼 代碼如下:
DELETE FROM cfa t
WHERE t.ROWID >
(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);
b情況，刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有rowid最小的記錄
復(fù)制代碼 代碼如下:
delete from cfa
where (businessid,customer) in (select businessid,customer
from cfa
group by businessid,customer
having count(*) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid,customer
having count(*) > 1);
或者，使用下面更簡(jiǎn)單高效的語(yǔ)句
復(fù)制代碼 代碼如下:
DELETE FROM cfa t
WHERE t.ROWID > (SELECT MIN(X.ROWID)
FROM cfa X
WHERE X.businessid = t.businessid
and x.customer = t.customer);
c情況，這種情況就比較簡(jiǎn)單，使用臨時(shí)表方法
復(fù)制代碼 代碼如下:
create table cfabak as select distinct * from cfa;
truncate table cfa;--如果是生產(chǎn)最好對(duì)該表backup
Insert into cfa select * from cfabak;
commit;
您可能感興趣的文章:- shell腳本操作oracle刪除表空間、創(chuàng)建表空間、刪除用戶(hù)
- Oracle誤刪除表數(shù)據(jù)后的數(shù)據(jù)恢復(fù)詳解
- oracle刪除表字段和oracle表增加字段
- Oracle刪除表前判斷表名是否存在若存在則刪除
- oracle查看表空間已分配和未分配空間的語(yǔ)句分享
- Oracle 查看表空間的大小及使用情況sql語(yǔ)句
- Oracle刪除表及查看表空間的實(shí)例詳解
