只选择并显示 MySQL 中的重复记录 [英] Select and display only duplicate records in MySQL

问题描述

这个问题很简单，我由于某种原因无法得到正确的结果来只显示重复的记录

This question is pretty simple I for some reason can't get the proper result to display only the duplicate records

Table   : Paypal_ipn_orders
id                              payer_email
1                               [e-mail]
2                               [e-mail]   
3                               [e-mail]
4                               [e-mail]
5                               [e-mail]

SELECT id, COUNT( payer_email ) `tot`
FROM paypal_ipn_orders
GROUP BY payer_email
HAVING `tot` >1

样本输出

id       tot
1         2
4         2

预期输出

id       payer_email 
1        [e-mail]
3        [e-mail]
4        [e-mail]
5        [e-mail]

我该如何做到这一点?

推荐答案

SELECT id, payer_email
FROM paypal_ipn_orders
WHERE payer_email IN (
    SELECT payer_email
    FROM paypal_ipn_orders
    GROUP BY payer_email
    HAVING COUNT(id) > 1
)

sqlfiddle

这篇关于只选择并显示 MySQL 中的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！