MySQL 如何根据MySQL中的多个字段查找重复行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12691327/
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
How to find duplicate rows based on multiple fields in MySQL?
提问by Codexer1912
Possible Duplicate:
Mysql Duplicate Rows ( Duplicate detected using 2 columns )
可能的重复：
Mysql 重复行（使用 2 列检测到重复）
In a MySQL database I have many rows. For example:
在 MySQL 数据库中，我有很多行。例如：
id | title | time | domain
32 title1 12:30 domain1.com
33 title1 12:30 domain2.com
34 title2 14:20 domain1.com
35 title3 14:30 domain2.com
36 title1 12:30 domain55.com
How am I able to select rows from a database based on onlytitle and time?Duplicate domains or ID's are not of concern, only the other two fields.
如何仅根据标题和时间从数据库中选择行？不关心重复的域或 ID，只关心其他两个字段。
I want to be able to retrieve rows 32, 33 and 36 because they have identical titles and identical times.
我希望能够检索第 32、33 和 36 行，因为它们具有相同的标题和相同的时间。
I don't want to have to put in a title or time, I want the query to return all fields where there's a "duplicate" match found on these two fields whether that be only two or 50. That way I can go through and edit or delete some of the duplicates.
我不想输入标题或时间，我希望查询返回在这两个字段上找到“重复”匹配项的所有字段，无论是两个还是 50 个。这样我就可以通过并编辑或删除一些重复项。
回答by kasi
Here is what you want
这是你想要的
SELECT title, time
FROM table
GROUP BY title, time
HAVING count(*) > 1
回答by Wolfgang
select distinct id, title, time
from table t1
where exists (select *
from table t2
where t2.id <> t1.id
and t2.title = t1.title
and t2.time = t1.time
)
