Oracle用rowid删除同一张表的重复记录

た入场券 2021-09-26 09:04 359阅读 0赞

经常有这样一个业务场景，我们在**同一张表中有重复的记录**，我们要删除重复的记录，这篇文章就是用来解决这个问题的，用的方法是**我目前遇到的效率最高的方法**（如果有更好的方法欢迎网友提供）。**这个方法会用到了rowid**，下面简单介绍一下rowid的定义

## 1、ROWID定义 ##

ROWID：数据库中行的全局唯一地址

对于数据中的每一行，rowid伪列返回行的地址。rowid值主要包含以下信息：

*  对象的数据对象编号
 *  该行所在的数据文件中的数据块
 *  该行中数据块的位置（第一行是0）
 *  数据行所在的数据文件（第一个文件是1）。该文件编号是相对于表空间。

通常来说，一个rowid值唯一标识数据中的一行。然而，存储在同一聚簇中不同的表可以有相同的rowid。

**2、实现的方法**

**方法一：**

![复制代码][copycode.gif]

DELETE FROM hr.employees
    WHERE ROWID IN (
                     SELECT ROWID
                     FROM (
                            SELECT first_name,
                                   last_name,
                                   ROWID,
                                   ROW_NUMBER() OVER(PARTITION BY first_name,last_name ORDER BY employee_id) AS staff_row --按照保留的唯一字段进行分区，取row_number
                                   FROM hr.employees
                          )
                     WHERE staff_row > 1
                   );

![复制代码][copycode.gif]

乍一看，我勒个去，这个是什么东西要，这么难懂！！没关系，下面给您解释一下，就很好懂了。

针对的是oracle内置的示例用户hr中的employees这张表，我们**希望的是first\_name和last\_name没有重复的项**（如果你的业务需要时别的话可以相应的转换，如在成绩表中的学号就是唯一个，就partition by学号），所以对这两个字段进行**partition  by**。

在子查询的子查询中我们选择的主要目的是rowid和row\_number（first\_name和last\_name只是用来辅助理解加进去的字段）；子查询中我们选择了row\_number > 1的rowid，这样的话按照first\_name和last\_name分组中**每一个分组只有一条记录没有被选择**；最外面的delete就直接把选择出来的rowid进行了删除。至此完成了hr.employees对于first\_name和last\_name的去重。

**　　**有朋友会说，妈蛋这太难理解了吧！**在这种情况下，往往有方法二~~~**

**方法二：**

DELETE FROM hr.employees t1
    WHERE t1.ROWID NOT IN (
                           SELECT MIN(t2.ROWID)
                           FROM hr.employees t2
                           GROUP BY t2.employee_id --按照想要唯一保留的字段进行分组
                          );

这个明显就比方法一好多了，子查询中我们先选除了rowid，然后按照我们**想要保留的唯一字段进行分组**，并取每组最小的rowid（注意是子查询表的rowid）；然后在用not in删除**除开最小的rowid以外的所有记录**。

怎么样，这个方法是不是瞬间解决并且非常好理解？**但是你以为这样就结束了？no no no**

**方法三：**

DELETE FROM hr.employees t1
    WHERE t1.rowid > (
                       SELECT MIN(t2.rowid)
                       FROM hr.employees t2
                       WHERE t1.employee_id = t2.employee_id --按照想要唯一保留的字段进行匹配
                     );

这个方式看起来和方法二差不多，但是想要说的是，**他用的是连接**，他用的是连接，不敢说连接一定比group by快，但是基本上不会输group by，而且在一般的情况下也是最快的了。**而且外层的">"可以用到索引**，就是各种快。

方法也同样说一下，子查询中按**照要保留的字段对t1和t2进行关联**，然后选择出最小的rowid（注意是子查询表的rowid），然后在外层用">"只保留每个匹配结果最小的一条记录。然后就瞬间删除重复的记录

[copycode.gif]: /images/20210923/ffac9bdf3c7c4e36885774c4daf144ee.png