首頁 > 資料庫 > Oracle > 主體

Oracle怎麼查詢重複字段

WBOY
發布: 2022-01-21 11:41:33
原創
8031 人瀏覽過

在Oracle中,可以利用count()函數來查詢重複字段,語法為「select * from 表名where 字段in(select 字段from 表名group by 字段having count(字段) >1) 」。

Oracle怎麼查詢重複字段

本教學操作環境: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教學

以上是Oracle怎麼查詢重複字段的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板