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?

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|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|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|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|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|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|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|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|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|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>

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

5位重磅专家聚焦 AI 时代开发者核心成长需求，带你深入解锁全栈进化、人机协作关键路径！点击报名到场：<a href="https://cloud.tencent.com/developer/salon/salon-2403/signup" target="_blank">https://cloud.tencent.com/developer/salon/salon-2403/signup</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/487c82639d2338df3ef66dfb1450601a.png"/>

腾讯技术创作特训营「年度论坛」正在报名中！

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

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

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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