blocks|key|295806|text|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
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|295807|sqlfiddle|unstyled|offset|length|295808|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/6d708/2^0|0|0|9|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>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) &gt; 1
)
</code></pre>

<a href="http://sqlfiddle.com/#!2/6d708/2" rel="noreferrer">sqlfiddle</a>

blocks|key|512281|text|在我的情况下，IN太慢了(180秒)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|512282|所以我改用了JOIN+(0.3秒)|512283|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|code-block|syntax|javascript|512284|entityMap^0|7|2|0|6|4|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

The <code>IN</code> was too slow in my situation (180 secs)

So I used a <code>JOIN</code> instead (0.3 secs)

<pre><code>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 ) &gt; 1
) j ON i.payer_email=j.payer_email
</code></pre>

blocks|key|512464|text|下面是一个简单的例子：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|512465|select+<duplicate_column_name>+from+<table_name>+group+by+<duplicate_column_name>+having+count(*)>=2|code-block|syntax|javascript|512466|它肯定会起作用。:)|512467|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

here is the simple example : 

<pre><code>select &lt;duplicate_column_name&gt; from &lt;table_name&gt; group by &lt;duplicate_column_name&gt; having count(*)&gt;=2
</code></pre>

It will definitly work. :)

blocks|key|295910|text|从表中获取所有重复行的列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|295911|Select+*+from+TABLE1+where+PRIMARY_KEY_COLUMN+NOT+IN+(+SELECT+PRIMARY_KEY_COLUMN
FROM+TABLE1+
GROUP+BY+DUP_COLUMN_NAME+having+(count(*)+>=+1))|code-block|syntax|javascript|295912|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Get a list of all duplicate rows from table:

<pre><code>Select * from TABLE1 where PRIMARY_KEY_COLUMN NOT IN ( SELECT PRIMARY_KEY_COLUMN
FROM TABLE1 
GROUP BY DUP_COLUMN_NAME having (count(*) &gt;= 1))
</code></pre>

blocks|key|295962|text|如果我想选择一个或多个列值，而该列值与两个行数据不同或可能相同，则上述答案将不起作用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|295963|对于Ex.我想选择用户名，出生日期也。但是在数据库中用户名不是重复的，但是出生日期是重复的，那么这个解决方案就不起作用了。|295964|对于此用途，此解决方案需要在同一表/上采用自连接|295965|SELECT++
++++distinct(p1.id),++p1.payer_email+,+p1.username,+p1.birth_date

FROM+
++++paypal_ipn_orders+AS+p1+

INNER+JOIN+paypal_ipn_orders+AS+p2+

ON+p1.payer_email=p2.payer_email

WHERE+

p1.birth_date=p2.birth_date|code-block|syntax|javascript|295966|以上查询将返回具有相同email_id和相同出生日期的所有记录|295967|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Hi above answer will not work if I want to select one or more column value which is not same or may be same for both row data 

For Ex. I want to select username, birth date also. But in database is username is not duplicate but birth date will be duplicate then this solution will not work.

For this use this solution 
Need to take self join on same table/

<pre><code>SELECT 
 distinct(p1.id), p1.payer_email , p1.username, p1.birth_date

FROM 
 paypal_ipn_orders AS p1 

INNER JOIN paypal_ipn_orders AS p2 

ON p1.payer_email=p2.payer_email

WHERE 

p1.birth_date=p2.birth_date
</code></pre>

Above query will return all records having same email_id and same birth date

blocks|key|512554|text|使用此代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|512555|+SELECT+*++
++++FROM++paypal_ipn_orders+
++++GROUP+BY++payer_email++
++++HAVING+COUNT(+payer_email)+>1++|code-block|syntax|javascript|512556|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

use this code 

<pre><code> SELECT * 
 FROM paypal_ipn_orders 
 GROUP BY payer_email 
 HAVING COUNT( payer_email) &gt;1 
</code></pre>

blocks|key|295814|text|SELECT+id,+payer_email+FROM+paypal_ipn_orders
WHERE+payer_email+IN+(
++++SELECT+payer_email+FROM+papypal_ipn_orders+GROUP+BY+payer_email+HAVING+COUNT(*)+>+1)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|295815|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT id, payer_email FROM paypal_ipn_orders
WHERE payer_email IN (
 SELECT payer_email FROM papypal_ipn_orders GROUP BY payer_email HAVING COUNT(*) &gt; 1)
</code></pre>

blocks|key|512390|text|这对我来说是最快的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|512391|SELECT
++++primary_key
FROM
++++table_name
WHERE
++++primary_key+NOT+IN+(
++++++++SELECT
++++++++++++primary_key
++++++++FROM
++++++++++++table_name
++++++++GROUP+BY
++++++++++++column_name
++++++++HAVING
++++++++++++COUNT(*)+=+1
++++);|code-block|syntax|javascript|512392|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works the fastest for me

<pre><code>SELECT
 primary_key
FROM
 table_name
WHERE
 primary_key NOT IN (
 SELECT
 primary_key
 FROM
 table_name
 GROUP BY
 column_name
 HAVING
 COUNT(*) = 1
 );
</code></pre>

blocks|key|296134|text|与this+answer类似，不过我使用的是临时表：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|296135|CREATE+TEMPORARY+TABLE+duplicates+(
++++SELECT+payer_email
++++FROM+paypal_ipn_orders
++++GROUP+BY+payer_email
++++HAVING+COUNT(id)+>+1
);
SELECT+id,+payer_email
FROM+paypal_ipn_orders+AS+p
INNER+JOIN+duplicates+AS+d+ON+d.payer_email=p.payer_email;|code-block|syntax|javascript|296136|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/21408164/4152012^0|1|B|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Similar to <a href="https://stackoverflow.com/a/21408164/4152012">this answer</a>, though I used a temporary table instead:

<pre><code>CREATE TEMPORARY TABLE duplicates (
 SELECT payer_email
 FROM paypal_ipn_orders
 GROUP BY payer_email
 HAVING COUNT(id) &gt; 1
);
SELECT id, payer_email
FROM paypal_ipn_orders AS p
INNER JOIN duplicates AS d ON d.payer_email=p.payer_email;
</code></pre>

blocks|key|512558|text|SELECT+*+FROM+`table`+t1+join+`table`+t2+WHERE+(t1.name=t2.name)+&&+(t1.id!=t2.id)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|512559|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT * FROM `table` t1 join `table` t2 WHERE (t1.name=t2.name) &amp;&amp; (t1.id!=t2.id)
</code></pre>

blocks|key|512433|text|我认为这种方式更简单。输出显示id和付款人的电子邮件，其中付款人的电子邮件在该表的多个记录中。结果按id排序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|512434|++++SELECT+id,+payer_email
++++FROM+paypal_ipn_orders
++++WHERE+COUNT(+payer_email+)>1
++++SORT+BY+id;|code-block|syntax|javascript|512435|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think this way is the simplier. The output displays the id and the payer's email where the payer's email is in more than one record at this table. The results are sorted by id.

<pre><code> SELECT id, payer_email
 FROM paypal_ipn_orders
 WHERE COUNT( payer_email )&gt;1
 SORT BY id;
</code></pre>

blocks|key|512198|text|尝试此查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|512199|SELECT+id,+COUNT(+payer_email+)+`tot`
FROM+paypal_ipn_orders
GROUP+BY+id
HAVING+`tot`+>1|code-block|syntax|javascript|512200|有帮助吗？|512201|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try this query: 

<pre><code>SELECT id, COUNT( payer_email ) `tot`
FROM paypal_ipn_orders
GROUP BY id
HAVING `tot` &gt;1
</code></pre>

Does it help?

This question is pretty simple I for some reason can't get the proper result to display only the duplicate records
<pre><code>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` &gt;1
</code></pre>
sample output
<pre><code>id tot
1 2
4 2
</code></pre>
expected output
<pre><code>id payer_email 
1 susan@gmail.com
3 susan@gmail.com
4 steve@gmail.com
5 steve@gmail.com
</code></pre>
How do I make this happen?

Select and display only duplicate records in MySQL

这个问题很简单，由于某种原因，我不能得到正确的结果，只显示重复的记录Table   : Paypal_ipn_ordersid                              payer_email1                               susan@gmail.com2                               ryan@gmail.com   

问在MySQL中选择并仅显示重复记录
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中选择并仅显示重复记录EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中选择并仅显示重复记录
EN