435
社区成员
发帖
与我相关
我的任务
分享mysql – 返回重复记录
我只想从表中返回重复记录.在我的情况下,如果多个记录在col1,col2,col3和col4中具有相同的值,则记录是重复的.
...全文
请发表友善的回复…
发表回复
weixin_38125291 2019-09-12
- 打赏
- 举报
SELECT col1, col2, col3, col4
, COUNT(*) AS cnt
FROM yourTable
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1
如果您想要显示其他列,可以将上面的内容加入到表中：
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
