在 MySQL 中按几列获取重复的行 [英] Getting duplicate rows by several columns in MySQL

问题描述

我正在尝试按大表(近 18 000 行)中的几列搜索重复行.问题是查询需要很多时间，我试过这个:

I'm trying to search duplicate rows by several columns in large table (near 18 000 rows). Problem is that queries take a lot of time, I tried this:

 SELECT * FROM table_name a, table_name b 
 WHERE a.col1 = b.col1 
 AND a.col2 = b.col2 
 AND a.col3 = b.col3
 AND a.col4 = b.col4 
 AND a.id <> b.id

还有这个:

SELECT *
FROM table_name 
WHERE col1 IN (
    SELECT col1 
    FROM table_name 
    GROUP BY col1 
    HAVING count(col1) > 1
    )
AND col2 IN (
    SELECT col2
    FROM table_name 
    GROUP BY col2
    HAVING count(col2) > 1
    )
AND col3 IN (
    SELECT col3
    FROM table_name 
    GROUP BY col3
    HAVING count(col3) > 1
    )
AND col4 IN (
    SELECT col4
    FROM table_name 
    GROUP BY col4
    HAVING count(col4) > 1
    )

它们都可以工作，但是太慢了.有什么想法吗?

they both work, but too slow. Any ideas?

推荐答案

您可以尝试使用一个联合 GROUP BY 语句，例如:

You can try using one joint GROUP BY statement like:

SELECT * FROM table_name
    GROUP BY col1, col2, col3, col4
    HAVING count(*) > 1

至少，它看起来会更干净.

At the very least, it will look cleaner.

编辑

将所有结果作为上一列的子集返回:

To return all results as a sub-set for the previous column:

SELECT *
FROM table_name 
WHERE col4 IN (
  SELECT col4
  FROM table_name 
  WHERE col3 IN (
    SELECT col3
    FROM table_name 
    WHERE col2 IN (
      SELECT col2
      FROM table_name
      WHERE col1 IN (
        SELECT col1
        FROM table_name
        GROUP BY col1
        HAVING count(col1) > 1
        )
      )
    )

从概念上讲，这应该会在更快的执行时间内为您提供所有结果.

This, in concept, should give you all results in a faster execution time.

这篇关于在 MySQL 中按几列获取重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！