在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