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

最新推荐文章于 2022-03-22 07:21:41 发布

最新推荐文章于 2022-03-22 07:21:41 发布 · 123 阅读

文章标签：

#Oracle #HTML

oracle 专栏收录该内容

60 篇文章

订阅专栏

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

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