MYSQL 重复条目 [英] MYSQL Duplicate entries

问题描述

我试图在我的 mysql 表中找到重复的条目.我想将不同的领域相互比较.这是我的表的结构:

i am trying to find duplicate entries within my mysql table. I would like to compare the different fields with each other. Here is the structure of my table:

ID    FirstName    LastName    Street    ZIP      City        IpAddress
 1      Jack        Smith       2nd     12345    Sample1     12.21.24.212
 2      Paul        Miller      3rd     45685    Sample2     78.54.85.654
 3      Jenny       Smith       3rd     77273    Sample3     84.91.67.311
 4      Frank       Jackson     1st     27819    Sample1     78.54.85.654
 5      Jack        Smith       3rd     72891    Sample2     94.79.99.465

现在我想分别比较街道和 ip 列，然后我想找到名字和姓氏的组合.我的表格中实际上还有几列我想搜索，但我认为上面的示例应该能让您了解我的计划.

Now i would like to compare the street and ip column individually and then i would like to find the combination of the first- and lastname. There are actually a few more columns in my table that i would like to search for but i think my example above should give you an idea about what i am planning.

我需要可能重复的条目的 ID 号.

I need the id numbers of the entries that could potencially duplicates.

在上面的例子中，当我比较名字和姓氏的组合时，输出应该是 id 号 1 和 5.

In the example above the output should be the id numbers 1 and 5 when i compare the combination of the first- and lastname.

如果我比较街道名称，输出应该是 id 编号 2,3 和 5.

The output should be the id numbers 2,3 and 5 if i compare the street names.

IP 地址的输出应该是 id 号 2 和 4.

And the output for the ip addresses should be id numbers 2 and 4.

有没有人对我应该如何做到这一点有一些想法?比较这些不同表格的最佳方法是什么?我不介意我是否必须进行多次查询.

Does anyone have some ideas about how i should do this? What is the best way to compare those different tables? I don't mind if i have to do several queries.

推荐答案

使用 GROUP_CONCAT() 获取组内的所有 ID，使用 GROUP BY 指定您要查找重复项的列.并且您可以使用 COUNT(*) 以便您只返回具有重复项的那些.

Use GROUP_CONCAT() to get all the IDs within a group, and GROUP BY to specify the columns that you're looking for duplidates of. And you can use COUNT(*) so you only return the ones that have duplicates.

对于街道:

SELECT street, GROUP_CONCAT(id)
FROM yourTable
GROUP BY street
HAVING COUNT(*) > 1

对于名字:

SELECT firstname, lastname, GROUP_CONCAT(id)
FROM yourTable
GROUP BY firstname, lastname
HAVING COUNT(*) > 1

这篇关于MYSQL 重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！