找出数据库表里重复记录和重复记录数的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