導讀:1建站知識案例：一個應用表中的一個字段是主鍵，向表中插入數據時，先把數據放在臨時表中（沒有主鍵）然后再插入應用表企業網站建設網站建設哪家好。

這時候如果臨時表中有重復數據，無論是主鍵字段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 cfawhere businessid in (select businessidfrom cfagroup by businessidhaving count(businessid) > 1)and rowid not in (select min(rowid)from cfagroup by businessidhaving count(businessid) > 1);

或者，使用下面更簡單高效的語句

復制代碼 代碼如下:

DELETE FROM cfa tWHERE t.ROWID >(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);

b情況，刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄

復制代碼 代碼如下:

delete from cfawhere (businessid,customer) in (select businessid,customerfrom cfagroup by businessid,customerhaving count(*) > 1)and rowid not in (select min(rowid)from cfagroup by businessid,customerhaving count(*) > 1);

關鍵詞標簽: 標簽 步驟