MYSQL重复条目

Question

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

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.

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

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

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

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.

Answer 1

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.

For streets:

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

For names:

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