如何在MySQL中基于多个字段查找重复行? [英] How to find duplicate rows based on multiple fields in MySQL?

问题描述

可能重复:
Mysql重复行(使用2列检测到重复)

在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

如何仅基于标题标题和时间从数据库中选择行?不必担心重复的域或ID，而仅关注其他两个字段./p>

我希望能够检索第32、33和36行，因为它们具有相同的标题和相同的时间.

我不想输入标题或时间，我希望查询返回所有在这两个字段上找到重复"匹配项的所有字段，无论是两个还是50.这样，我可以查看并编辑或删除其中一些重复项.

解决方案

这就是您想要的

SELECT title, time  
  FROM table
GROUP BY title, time
  HAVING count(*) > 1

Possible Duplicate:
Mysql Duplicate Rows ( Duplicate detected using 2 columns )

In a MySQL database I have many rows. For example:

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 only title and time? Duplicate domains or ID's are not of concern, only the other two fields.

I want to be able to retrieve rows 32, 33 and 36 because they have identical titles and identical times.

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.

解决方案

Here is what you want

SELECT title, time  
  FROM table
GROUP BY title, time
  HAVING count(*) > 1

这篇关于如何在MySQL中基于多个字段查找重复行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！