Oracle怎樣查詢(xún)重復(fù)字段

oracle中,可以利用count()函數(shù)來(lái)查詢(xún)重復(fù)字段,語(yǔ)法為“select * from 表名 where 字段 in(select 字段 from 表名 group by 字段 having count(字段) >1)”。

Oracle怎樣查詢(xún)重復(fù)字段

本教程操作環(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)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊6 分享