在oracle中查看重复行的所有数据 [英] view all data for duplicate rows in oracle

问题描述

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

I've got a table with 6 columns:

• id
• 名称
• type_id
• 代码
• lat
• 长

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

前三个是必需的. ID 是私钥，会随序列自动插入.

The first three are required. ID is the private key, inserted automatically with a sequence.

我有一些重复的行，如 name 和 type_id 所定义的一样，但是我想查看所有重复数据.我可以很容易地找到骗子:

I have some rows that are duplicates, as defined by BOTH the name and type_id being equal, but i'd like to view all the data for the dupes. I can find the dupes simply enough:

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

但实际上查看所有信息令我感到困惑.我知道这应该很简单，但是我在这里碰壁.

but actually viewing all the info is confounding me. I know this should be simple, but I'm hitting a wall here.

推荐答案

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

You can always use the GROUP BY/ HAVING query in an IN clause. This works and is relatively straightforward but it may not be particularly efficient if the number of duplicate rows is relatively large.

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

使用分析函数通常会更有效率，以避免再次点击表格.

It would generally be more efficient to use analytic functions in order to avoid hitting the table a second time.

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

根据您打算如何处理数据以及特定行可能有多少重复项，您可能还想将 table1 与其自身结合以在一行中获取数据

Depending on what you are planning to do with the data and how many duplicates of a particular row there might be, you also might want to join table1 to itself to get the data in a single row

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)

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