在 MySQL 中的列上查找具有相同值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1786533/
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
Find rows that have the same value on a column in MySQL
提问by bobo
In a [member] table, some rows have the same value for the emailcolumn.
在 [member] 表中，某些行的列具有相同的值email。
login_id | email
---------|---------------------
john | [email protected]
peter | [email protected]
johnny | [email protected]
...
Some people used a different login_id but the same email address, no unique constraint was set on this column. Now I need to find these rows and see if they should be removed.
有些人使用了不同的 login_id 但使用了相同的电子邮件地址，此列没有设置唯一约束。现在我需要找到这些行，看看是否应该删除它们。
What SQL statement should I use to find these rows? (MySQL 5)
我应该使用什么 SQL 语句来查找这些行？(MySQL 5)
回答by Scott Saunders
This query will give you a list of email addresses and how many times they're used, with the most used addresses first.
此查询将为您提供电子邮件地址列表及其使用次数，最常使用的地址在前。
SELECT email,
count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC
If you want the full rows:
如果你想要完整的行：
select * from table where email in (
select email from table
group by email having count(*) > 1
)
回答by HLGEM
select email from mytable group by email having count(*) >1
回答by Ivan Nevostruev
Here is query to find email's which are used for more then one login_id:
这是用于 findemail的查询，用于多个login_id：
SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1
You'll need second (of nested) query to get list of login_idby email.
您需要第二个（嵌套的）查询来获取login_idby列表email。
回答by Sergey Makhonin
First part of accepted answer does not work for MSSQL.
This worked for me:
已接受答案的第一部分不适用于 MSSQL。
这对我有用：
select email, COUNT(*) as C from table
group by email having COUNT(*) >1 order by C desc
回答by ramesh kumar
use this if your email column contains empty values
如果您的电子邮件列包含空值，请使用此选项
select * from table where email in (
select email from table group by email having count(*) > 1 and email != ''
)
回答by Marc L
I know this is a very old question but this is more for someone else who might have the same problem and I think this is more accurate to what was wanted.
我知道这是一个非常古老的问题，但对于可能有同样问题的其他人来说更是如此，我认为这更符合我们想要的。
SELECT * FROM member WHERE email = (Select email From member Where login_id = [email protected])
This will return all records that have [email protected] as a login_id value.
这将返回所有具有 [email protected] 作为 login_id 值的记录。
回答by Libertine
Thanks guys :-) I used the below because I only cared about those two columns and not so much about the rest. Worked great
谢谢大家 :-) 我使用了下面的内容，因为我只关心这两列，而不太关心其余的列。工作得很好
select email, login_id from table
group by email, login_id
having COUNT(email) > 1
回答by Suba Karthikeyan
Get the entire record as you want using the condition with inner select query.
使用带有内部选择查询的条件，根据需要获取整个记录。
SELECT *
FROM member
WHERE email IN (SELECT email
FROM member
WHERE login_id = [email protected])
