只选择并显示 MySQL 中的重复记录

Select and display only duplicate records in MySQL(只选择并显示 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

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