查找部分字段重复的记录 ORACLE Identifying duplicate rows

http://www.jlcomp.demon.co.uk/faq/duplicates.html
http://www.arikaplan.com/oracle/ari90897.html

Identifying duplicate rows

It is possible to determine duplicate rows using the select with a count of all the rows which have the same values in the 'unique' fields as follows :

 

        select a,b,count(*)

        from test

        group by a,b

        having count(*) > 1; 

The above script will produce an output something like the following :

 

                 A          B   COUNT(*)

        ---------- ---------- ----------

                 1          2        259

                 2          2          5

From this we can see that the so-called unique columns a and b in this table are nowhere near unique. We need to get rid of 258 and 4 rows respectively to make the table properly unique. See below for a couple of methods of how to do the deletions.