选择并仅显示mysql中的重复记录

小编典典

选择并仅显示mysql中的重复记录

mysql

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

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

我如何做到这一点？

阅读 243

2020-05-17

共1个答案

小编典典

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

2020-05-17