Oracle-删除重复项 [英] Oracle - deleting duplicates

问题描述

我发现以下删除重复项的方法:

I have found the following way for removing duplicates:

DELETE FROM
   table_name A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        table_name B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        ); 

有人可以逐步解释我查询的工作原理吗?

Could someone explain me step by step how does the query works?

TIA！

推荐答案

在Oracle中，ROWID是指向行的物理位置的伪列.该查询进行自我连接，并获取与列1和列2的值相同的行.第2列-假设这些键足以标识为重复行.

In Oracle, ROWID is a pseudo column points to the physical location of a row. The query does a self join and fetches those rows which have the same value of column 1 & column 2 - with the assumption that these keys are enough to identify as duplicate row.

一旦获取了行，查询就会删除那些大于所获取的第一行的行ID，从而删除重复行

Once the rows are fetched, the query then deletes those rowids which are larger than the first row fetched, thereby deleting duplicates

这篇关于Oracle-删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！