返回重复记录 [英] Return duplicate records

问题描述

我只想从表中返回重复的记录。在我的情况下，如果在col1，col2，col3和col4中有多个记录具有相同的值，则记录是重复的。

解决方案

pre> SELECT col1，col2，col3，col4
，COUNT（*）AS cnt
FROM yourTable
GROUP BY col1，col2，col3，col4
HAVING COUNT（*）> 1


如果有其他列要显示，您可以 JOIN 以上表格：

  SELECT t。* 
，dup.cnt 
 FROM yourTable t 
 JOIN 
（SELECT col1，col2，col3，col4 
，COUNT（*）AS cnt 
 FROM yourTable 
 GROUP BY col1， col2，col3，col4 
 HAVING COUNT（*）> 1 
）AS dup 
 ON t.col1 = dup.col1 
 AND t.col2 = dup.col2 
 AND t.col3 = dup.col3 
 AND t.col4 = dup.col4 
  

I simply want to return duplicate records from a table. In my case, a record is duplicate if more than one record has the same value in col1, col2, col3, and col4.

解决方案

SELECT col1, col2, col3, col4
     , COUNT(*) AS cnt
FROM yourTable
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1

If there are additional columns that you want to be shown, you can JOIN the above to the table:

SELECT t.*
     , dup.cnt
FROM yourTable t
    JOIN
      ( SELECT col1, col2, col3, col4
             , COUNT(*) AS cnt
        FROM yourTable
        GROUP BY col1, col2, col3, col4
        HAVING COUNT(*) > 1
      ) AS dup
      ON  t.col1 = dup.col1
      AND t.col2 = dup.col2
      AND t.col3 = dup.col3
      AND t.col4 = dup.col4

这篇关于返回重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！