如何列出数据库中的重复条目

问题描述：

how can i write a query to list duplicate entries in a database from the same category. The duplicates have the same value in the "name" column. I need to list only the duplicates in the same category so I can then delete the duplicate.

I am using this example from a search

 SELECT email FROM tableName GROUP BY email HAVING count(email) > 1

That works for getting duplicates but it gets all duplicates, how can i rewrite it to get the duplicates from the same categories. In the above example, if i have an email that exists in the cat 1 and cat 4, it will be shown as duplicate, which is not the case. It should only list duplicates if the email exists twice or more in cat 1, or twice and more in cat 4 and so on.

Thanks.

如何编写查询以列出同一类别的数据库中的重复条目。重复项在“名称”列中具有相同的值。我只需列出同一类别中的重复项，以便我可以删除副本。 p>

我在搜索中使用此示例 p>

  SELECT email FROM tableName GROUP BY email HAVING count（email）＆gt;  1 
  code>  pre> 
 
 这适用于获取重复项，但它会获得所有重复项，如何重写它以获取相同类别的重复项。
在上面的示例中，如果 我有一个存在于猫1和猫4中的电子邮件，它将显示为重复，但情况并非如此。 如果电子邮件在cat 1中存在两次或更多，或者在cat 4中存在两次或更多，则它应该只列出重复项。依此类推。 p> 
 
 
谢谢。 p> 
  div>

答

You can add more than one column to a group by. I.E.

SELECT email, category from tableName GROUP BY email, category HAVING count(email) > 1

That will show the email and category only where the email and category are both duplicate (I.E. same email twice with same category).

答

Add the category to the group by.

SELECT email FROM tableName GROUP BY email, category HAVING count(email) > 1

The only thing wrong with this is you won't be able to tell which category the duplicate is in unless you SELECT on it as well.

如何列出数据库中的重复条目

相关推荐