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

iteye_16348

于 2008-03-13 08:49:10 发布

阅读量113

点赞数

分类专栏： oracle 文章标签： Oracle HTML

oracle 专栏收录该内容

60 篇文章

订阅专栏

本文介绍了一种使用SQL查询来识别数据库中具有重复唯一字段值的方法。通过一个具体的例子展示了如何找到并处理这些重复项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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


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.