找出重復記錄SQL

 

如果TABLE1有两个column COL1和COL2,那么下面的SQL可以找出TABLE1里的重复记录和重复记录数

SELECT COUNT(*) AS RowNumber,COL1,COL2
FROM TABLE1 T1
WHERE
(SELECT COUNT(*)
FROM TABLE1 T2
WHERE T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2)>1
GROUP BY COL1,COL2