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

发布于 2024-12-23 18:18:05 字数 516 浏览 3 评论 0原文

我有一个包含 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

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

原文

I've got a table with 6 columns:

id
name
type_id
code
lat
long

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初见终念 2024-12-30 18:18:05

您始终可以在 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)

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

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)

回复收藏 0 原文

一曲琵琶半遮面シ 2024-12-30 18:18:05

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 )

会做的。

华泰

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 )

Would do it.

HTH

回复收藏 0 原文

无人问我粥可暖 2024-12-30 18:18:05

您可以在表上执行自连接以查找所有重复项对：

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。如果您使用不同的数据库，则需要采用不同的方法将它们分开。

You can do a self join on the table to find all pairs of duplicates:

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

To make sure that a row does not match itself and each pair is only output once, I added a.ROWID > b.ROWID, which works for Oracle. You will need a different way to keep them apart if you use a different database.

回复收藏 0 原文