在 Oracle SQL 中查找列中的重复项

Question

我已经搜索了查找列中的重复条目和Oracle：在选择查询中查找重复行，但似乎没有得到任何答案...

我有一些数据看起来像这样。

columnA    columnB    columnC
1111111    emailID1   true
1111111    emailID2   false
1111222    emailID3   true
1111339    emailID4   true
2384398    emailID5   true

我希望只显示那些在列A中具有相同值但在列B和/或C中可能不同的列。

columnA    columnB    columnC
1111111    emailID1   true
1111111    emailID2   false

使用having >1似乎并不能很好地表达这个意思，有什么想法吗？谢谢。

- prog rice bowl

---

顺便说一下，各位，我只在一个表格上搜索，不需要进行连接操作。 :) - prog rice bowl

一些提出的解决方案涉及自连接，因此连接是您问题的有效解决方案。 - Jeffrey Kemp

Answer 1

使用having count(*) > 1只是解决问题的一半。另一半是获取相应的行。

你可以这样做：

SELECT *
FROM MyTable
WHERE ColumnA IN (
    SELECT ColumnA FROM MyTable GROUP BY ColumnA HAVING COUNT(*) > 1
)

- Sergey Kalinichenko

---

+1，但你也可以通过连接子查询来实现，这会更具扩展性。 - Bohemian

Answer 2

试试这个：

SELECT t.*
FROM (SELECT ColumnA FROM MyTable GROUP BY ColumnA HAVING COUNT(*) > 1) dups
JOIN MyTable t ON t.ColumnA = dups.ColumnA

只要在ColumnA上创建索引，这也可以很好地扩展：

create index MyTable_ColumnA on MyTable(ColumnA);

这样的索引将用于主查询和内部查询，从而使性能非常好。

- Bohemian

---

虽然我更喜欢 IN 的外观，但我完全同意 JOIN 使您不那么依赖于优化器。 - Sergey Kalinichenko

当然不是 - 在这种情况下，建议是将一个表连接到自身。内部查询标识重复行，外部查询收集您想要的其他列以得到结果。 - Jeffrey Kemp

@progricebowl 如果你执行这个查询，你会发现它会给你想要的结果。连接是在重复的ColumnA值列表与表本身之间进行的，以给你行。我坚信这个查询将为你提供任何查询中最佳的性能。 - Bohemian

@Bohemian 我尝试了这个查询和其他查询，但总是得到“不是分组表达式”的错误提示？ - Jan

@taclight，你正在使用哪个数据库和版本？ - Bohemian

Answer 3

我通常喜欢在查询中避免多次访问表格 - 即使没有索引，这也可以很好地工作 - 只需对表格进行一次扫描：

SELECT columnA, columnB, columnC
FROM (SELECT mytable.*
            ,COUNT(*) OVER (PARTITION BY columnA) countA
      FROM mytable)
WHERE countA > 1

- Jeffrey Kemp

Answer 4

SELECT T.columnA, S.columnB, S.columnC FROM
(
SELECT columnA FROM someTable 
GROUP BY columnA
HAVING COUNT(*) > 1
) T INNER JOIN someTable S ON T.columnA = S.columnA 

- realnumber3012

Answer 5

你可以使用分析函数来完成此操作。找到最小值和最大值，返回它们不同的行：

select columnA, columnB, columnC
from (select t.*,  min(t.columnC) over (partition by columnA, columnB) as minC,
             max(t.columnC) over (partition by columnA, columnB) as maxC
      from t
     ) t
where minC <> maxC;

- Gordon Linoff

Answer 6

这个帖子可能有点老了，但是更新一下大家更好/高效的查找重复记录的解决方案还是值得的。您可以使用分区来查找匹配列上的重复记录（使用尽可能多的列而不使用内部连接）。

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY your_key_column) AS duplicate_count
        FROM    yourtable t
        )
WHERE   duplicate_count > 1 --get duplicate records for the matching value in column1 and column2

请查看@Quassnoi原始答案此处。感谢他，通过使用partition找到了一个非常聪明的解决方案。

- Estin Chin

Answer 7

如果您正在寻找数据库中具有唯一ID的条目，其中列中可以出现多个键，则查找它们的简单方法是创建两个表，如下所述：

这里：TICKETID是主键，TKTNUMBER可以多次出现。

CREATE TABLE TEMP
(
   TICKETID    FLOAT,
   TKTNUMBER   FLOAT
);

CREATE TABLE TEMP2
(
   TKTNUMBER   FLOAT,
   COUNTER     INTEGER
);

通过仅查看COUNT（TKTNUMBER）> 1的TKTNUMBERS，将所有TICKETID和TKTNUMBER放入：

INSERT INTO TEMP
   SELECT 
       TICKETID, 
       TKTNUMBER
   FROM YOUR_TABLE
   WHERE TKTNUMBER IN (  
            SELECT TKTNUMBER
            FROM YOUR_TABLE
            HAVING COUNT (TKTNUMBER) > 1
            GROUP BY TKTNUMBER);

最后，要查看计数器，请按照上述方式输入TKTNUMBER和COUNT。

INSERT INTO TEMP2
    SELECT 
        TKTNUMBER, 
        COUNT (TKTNUMBER) AS COUNTER
    FROM YOUR_TABLE
    HAVING COUNT (TKTNUMBER) > 1
    GROUP BY TKTNUMBER
    ORDER BY 2 DESC

您可以按如下方式进行选择（通过将两个表格连接在tktnumber上）：

SELECT 
    T1.TICKETID,
    T1.TKTNUMBER,
    T2.COUNTER
FROM 
    TEMP T1 INNER JOIN 
    TEMP2 T2 ON 
        T2.TKTNUMBER = T1.TKTNUMBER
ORDER BY T2.COUNTER DESC

- V. Wolf