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.
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!