How to query duplicate fields in Oracle

In Oracle, you can use the count() function to query repeated fields. The syntax is "select * from table name where field in (select field from table name group by field having count (field) >1) ".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query duplicate fields in Oracle

1. Find redundant duplicate records in the table. Duplicate records are judged based on a single field (userCode)

select 
* 
from 
user
where 
userCode
in 
(select  userCode  from  user group by  userCode having count (userCode) > 1)

2. Delete the table Unnecessary duplicate records, duplicate records are judged based on a single field (userCode), only the record with the smallest rowid is left

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. Unnecessary duplicate records (multiple fields) in the lookup table

select 
* 
from 
user a
where 
(a.userCode,a.userName) 
in  
(select userCode,userName from user group by userCode,userName having count(*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest 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. Find the redundant duplicate records (multiple fields) in the table, excluding the record with the smallest rowid. Record

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)

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to query duplicate fields in Oracle. For more information, please follow other related articles on the PHP Chinese website!