在oracle中，可以利用count()函數配合select查詢語句來查詢重復的數據，語法為“select userCode from user group by userCode having count(userCode)>1”。
本教程操作環境：Windows10系統、Oracle 11g版、Dell G3電腦。
oracle怎么查詢重復的數據
1、查找表中多余的重復記錄，重復記錄是根據單個字段（userCode）來判斷
select? ????*? from? ????user where? ????userCode in? ????(select??userCode??from??user?group?by??userCode?having?count?(userCode)?>?1)
2、刪除表中多余的重復記錄，重復記錄是根據單個字段（userCode）來判斷，只留有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、查找表中多余的重復記錄（多個字段）
select? ????*? from? ????user?a where? ????(a.userCode,a.userName)? in?? ????(select?userCode,userName?from?user?group?by?userCode,userName?having?count(*)?>?1)
4、刪除表中多余的重復記錄（多個字段），只留有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、查找表中多余的重復記錄（多個字段），不包含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視頻教程》
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
喜歡就支持一下吧
相關推薦
