MySQL 在mysql中只选择并显示重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11694761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select and display only duplicate records in mysql
提问by user1542036
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 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
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 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
How do I make this happen?
我该如何做到这一点？
回答by lc.
回答by Timo Huovinen
The INwas too slow in my situation (180 secs)
将IN在我的处境太慢（180秒）
So I used a JOINinstead (0.3 secs)
所以我改用了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
回答by Gaurav Gupta
here is the simple example :
这是一个简单的例子：
select <duplicate_column_name> from <table_name> group by <duplicate_column_name> having count(*)>=2
It will definitly work. :)
它肯定会起作用。:)
回答by kasiviswanatham
Get a list of all duplicate rows from table:
从表中获取所有重复行的列表：
Select * from TABLE1 where PRIMARY_KEY_COLUMN NOT IN ( SELECT PRIMARY_KEY_COLUMN
FROM TABLE1
GROUP BY DUP_COLUMN_NAME having (count(*) >= 1))
回答by Sameer Kazi
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/
对于此用途，此解决方案需要在同一张表上进行自联接/
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
Above query will return all records having same email_id and same birth date
以上查询将返回所有具有相同 email_id 和相同出生日期的记录
回答by M.Ganji
use this code
使用此代码
SELECT *
FROM paypal_ipn_orders
GROUP BY payer_email
HAVING COUNT( payer_email) >1
回答by user3237205
SELECT * FROM `table` t1 join `table` t2 WHERE (t1.name=t2.name) && (t1.id!=t2.id)
回答by jdenoc
Similar to this answer, though I used a temporary table instead:
类似于这个答案，虽然我使用了一个临时表：
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;
回答by Incognito
This works the fastest for me
这对我来说是最快的
SELECT
primary_key
FROM
table_name
WHERE
primary_key NOT IN (
SELECT
primary_key
FROM
table_name
GROUP BY
column_name
HAVING
COUNT(*) = 1
);
回答by Filippos
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.
我认为这种方式更简单。输出显示 id 和付款人的电子邮件，其中付款人的电子邮件在此表中的多个记录中。结果按id排序。
SELECT id, payer_email
FROM paypal_ipn_orders
WHERE COUNT( payer_email )>1
SORT BY id;
