How to query duplicate data in oracle

In Oracle, you can use the count() function with the select query statement to query repeated data. The syntax is "select userCode from user group by userCode having count(userCode)>1".

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

How to query duplicate data 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 Duplicate records are judged based on a single field (userCode), leaving only the record with the smallest 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. Lookup the extra duplicate records (multiple fields) in the 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 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 redundant duplicate records (multiple fields) in the table, excluding rowid The smallest 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 Video Tutorial"

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