在Oracle中查找重复数据 [英] Finding Duplicate Data in Oracle

问题描述

我有一个包含500,000条以上记录的表格，以及ID，名字，姓氏和电子邮件地址的字段。我想做的是找到名字和姓氏都是重复的行（因为在同一个人有两个单独的ID，电子邮件地址或任何，他们在表中多次）。我想我知道如何使用GROUP BY找到重复的，这是我有：

I have a table with 500,000+ records, and fields for ID, first name, last name, and email address. What I'm trying to do is find rows where the first name AND last name are both duplicates (as in the same person has two separate IDs, email addresses, or whatever, they're in the table more than once). I think I know how to find the duplicates using GROUP BY, this is what I have:

SELECT first_name, last_name, COUNT(*)
FROM person_table
GROUP BY first_name, last_name
HAVING COUNT(*) > 1

问题是，我需要将具有这些重复名称的整行移动到不同的表。有没有办法找到重复和获取整行？还是至少得到ID？我尝试使用自连接，但回到更多的行比表中开始。这是一个更好的方法吗？

The problem is that I need to then move the entire row with these duplicated names into a different table. Is there a way to find the duplicates and get the whole row? Or at least to get the IDs as well? I tried using a self-join, but got back more rows than were in the table to begin with. Would that be a better approach? Any help would be greatly appreciated.

推荐答案

（first_name，last_name）上的索引或（last_name，first_name）将有助于：

SELECT t.*
FROM 
    person_table t
  JOIN      
      ( SELECT first_name, last_name
        FROM person_table
        GROUP BY first_name, last_name
        HAVING COUNT(*) > 1
      ) dup
    ON  dup.last_name = t.last_name
    AND dup.first_name = t.first_name

或：

SELECT t.*
FROM person_table t
WHERE EXISTS
      ( SELECT *
        FROM person_table dup
        WHERE dup.last_name = t.last_name
          AND dup.first_name = t.first_name
          AND dup.ID <> t.ID
      )

这篇关于在Oracle中查找重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！