.jpg)
在oracle中，可以利用count()函數(shù)來(lái)查詢(xún)重復(fù)字段，語(yǔ)法為“select * from 表名 where 字段 in(select 字段 from 表名 group by 字段 having count(字段) >1)”。
本教程操作環(huán)境：Windows10系統(tǒng)、Oracle 11g版、Dell G3電腦。
Oracle怎樣查詢(xún)重復(fù)字段
1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（userCode）來(lái)判斷
select? *? from? user where? userCode in? (select??userCode??from??user?group?by??userCode?having?count?(userCode)?>?1)
2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（userCode）來(lái)判斷，只留有rowid最小的記錄
delete?from? user? where? userCode? in? (select?userCode?from?user?group?by??userCode?having?count?(peopleId)?>?1) and?rowid?not?in? (select?min(rowid)?from???user?group?by?userCode?having?count(userCode)>1)
3、查找表中多余的重復(fù)記錄（多個(gè)字段）
select? *? from? user?a where? (a.userCode,a.userName)? in?? (select?userCode,userName?from?user?group?by?userCode,userName?having?count(*)?>?1)
4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有rowid最小的記錄
delete?from? user?a where (a.userCode,a.userName)? in??? (select?userCode,userName?from?user?group?by?userCode,userName?having?count(*)?>?1) and?rowid?not?in? (select?min(rowid)?from?user?group?by?userCode,userName?having?count(*)>1)
5、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄
select? *? from? user?a where? (a.userCode,a.userName)?? in??? (select?userCode,userName?from?user?group?by?userCode,userName?having?count(*)?>?1) and?rowid?not?in? (select?min(rowid)?from?user?group?by?userCode,userName?having?count(*)>1)
推薦教程：《Oracle教程》
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。
THE END
.png)
