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

问题描述

这个问题很简单，因为某些原因，不能得到正确的结果，只显示重复记录。

 表：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 steve @ gmail.com 
 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 
）
  / pre> 
 
 
  sqlfiddle  
 
This question is pretty simple I for some reason cant 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
sample output 
id       tot
1         2
4         2
expected output
id       payer_email 
1        [e-mail]
3        [e-mail]
4        [e-mail]
5        [e-mail]
How do I make this happen?  
解决方案
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屋！