这个问题很简单,由于某种原因,我不能得到正确的结果,只显示重复的记录
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
我怎样才能做到这一点?
发布于 2012-07-28 03:56:04
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
)
发布于 2014-01-28 22:23:04
在我的情况下,IN
太慢了(180秒)
所以我改用了JOIN
(0.3秒)
SELECT i.id, i.payer_email
FROM paypal_ipn_orders i
INNER JOIN (
SELECT payer_email
FROM paypal_ipn_orders
GROUP BY payer_email
HAVING COUNT( id ) > 1
) j ON i.payer_email=j.payer_email
发布于 2015-12-17 18:38:33
下面是一个简单的例子:
select <duplicate_column_name> from <table_name> group by <duplicate_column_name> having count(*)>=2
它肯定会起作用。:)
https://stackoverflow.com/questions/11694761
复制相似问题