這時候如果臨時表中有重複資料，無論是主鍵字段businessid有重複，還是一整行有重複都會報出違反唯一主鍵限制錯誤。
方法：group by XX having count(*)>1,rowid,distinct,temporary table,procedure
1、查詢表中的重複資料
a.重複一個字段
b.重複多個字段
c.重複一整行
建立測試表：
複制代碼 代碼如下:
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');
---------------以下為重複資料----------------------------------------------
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');
對于a的情況,隻有businessid重複
複制代碼 代碼如下:
select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);
如果是b的情況，businessid 和name同時存在重複
複制代碼 代碼如下:
select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);
對于c的情況，重複一整行
參考b的方法：
複制代碼 代碼如下:
select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1);
2、删除表中的重複資料
a情況，删除表中多餘的重複記錄，重複記錄是根據單個字段(businessid)來判斷，隻留有rowid最小的記錄
也可以隻保留rowid不是最小記錄，需要把代碼中的min改為max這裡不再贅述。
複制代碼 代碼如下:
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);
或者，使用下面更簡單高效的語句
複制代碼 代碼如下:
DELETE FROM cfa t
WHERE t.ROWID >
(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);
b情況，删除表中多餘的重複記錄(多個字段)，隻留有rowid最小的記錄
複制代碼 代碼如下:
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);
或者，使用下面更簡單高效的語句
複制代碼 代碼如下:
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情況，這種情況就比較簡單，使用臨時表方法
複制代碼 代碼如下:
create table cfabak as select distinct * from cfa;
truncate table cfa;--如果是生産最好對該表backup
Insert into cfa select * from cfabak;
commit;
免責聲明：本站所有文章和圖檔均來自使用者分享和網絡收集，文章和圖檔版權歸原作者及原出處所有，僅供學習與參考，請勿用于商業用途，如果損害了您的權利，請聯系網站客服處理。