查看Oracle中重复行的所有数据

Question

我有一个包含6个列的表:

• id
• name
• type_id
• code
• lat
• long

前三个是必须的。 ID 是私有密钥，由序列自动插入。

我有一些重复的行，由于 name 和 type_id 都相等而定义为重复，但是我想查看所有重复数据的信息。 我可以很容易地找到重复项:

SELECT   name 
       , type_id
FROM   table1
GROUP BY name 
         , type_id
HAVING COUNT(*) > 1

但实际上，查看所有信息让我感到困惑。我知道这应该很简单，但我在这里遇到了难题。

- Marc

Answer 1

您可以在IN子句中始终使用GROUP BY/HAVING查询。这是有效的且相对简单，但如果重复行的数量相对较大，则可能不太高效。

SELECT *
  FROM table1
 WHERE (name, type_id) IN (SELECT name, type_id
                             FROM table1
                            GROUP BY name, type_id
                           HAVING COUNT(*) > 1)

通常使用分析函数可以更有效地避免第二次访问表。

SELECT *
  FROM (SELECT id, 
               name,
               type_id,
               code,
               lat,
               long,
               count(*) over (partition by name, type_id) cnt
          FROM table1)
 WHERE cnt > 1

根据您计划对数据执行的操作以及可能存在的特定行的重复次数，您可能还希望将 table1 自连接以便在单个行中获取数据。

SELECT a.name,
       a.type_id,
       a.id,
       b.id,
       a.code,
       b.code,
       a.lat,
       b.lat,
       a.long,
       b.long
  FROM table1 a
       JOIN table1 b ON (a.name = b.name AND
                         a.type_id = b.type_id AND
                         a.rowid > b.rowid)

- Justin Cave

---

我的第一反应是使用第一个查询，但我不确定是否可以在IN子句中使用两个列。我想我应该尝试一下。谢谢。 - Marc

Answer 2

SELECT * 
FROM   table1 t1 
WHERE  (t1.name,t1.type_id) in ( SELECT DISTINCT name
                                               , type_id
                                 FROM     table1
                                 GROUP BY name, type_id
                                 HAVING COUNT(*) > 1 )

可以做。

希望对你有所帮助。

- Zsolt Botykai

Answer 3

你可以在表上进行自连接，以查找所有重复对：

SELECT 
  a.name    name
, a.type_id type_id_a
, a.code    code_a
, a.lat     lat_a
, a.long    long_a
, b.code    code_b
, b.lat     lat_b
, b.long    long_b
FROM table1 a
JOIN table1 b
ON  a.name    = b.name
AND a.type_id = b.type_id
AND a.ROWID > b.ROWID

为了确保一行不与自身匹配，并且每对只输出一次，我添加了 a.ROWID > b.ROWID，这适用于 Oracle。如果您使用不同的数据库，则需要另一种方法来区分它们。

- The Nail

---

大概，您想要一个额外的连接条件来确保从A和B返回的行实际上是不同的行。例如AND a.id > b.id或AND a.rowid > b.rowid。 - Justin Cave

Answer 4

如果比较的字段中有一个是NULL值，那么这仍然无法找到重复项。为了找到这些重复项，我使用nvl函数将比较字段中的NULL值替换为一个我知道在该表/字段中不可能出现的值。

- Catherine Sudres

Answer 5

只需将NULLS变为0...

...使用NVL函数。

- Rick