只选择并显示 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                               susan@gmail.com
2                               ryan@gmail.com   
3                               susan@gmail.com
4                               steve@gmail.com
5                               steve@gmail.com

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        susan@gmail.com
3        susan@gmail.com
4        steve@gmail.com
5        steve@gmail.com

我该如何做到这一点?

推荐答案

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