Query data with the same field values in a table

To query data with the same field values in a table.

 

Example:

 

For example, the following table is available: person

 

We can see that the values of name and city are the same in the data rows of ID = 1 and ID = 5, id = 2 and ID = 6. How can we query it?

 

Take a look at the following SQL statement:

 

Select p1.name, p1.city from person P1, person P2 where p1.name = p2.name and p1.city = p2.city;

 

This statement uses its own link to query a single table. But the result is as follows:

We can see that this is not the expected result. The error is that the same data row in a table is compared to meet the condition. Therefore, it is output.

 

 

Therefore, you can use the following solution:

 

Select p1.name, p1.city from person P1, person P2 where p1.name = p2.name and p1.city = p2.city and p1.id! = P2.id;

 

Let's take a look at the results:

 

 

Meet our requirements. This method can be expanded to 1 field, 2 fields, 3 fields, or even more.

 

In some software systems, two or three software systems are not allowed... If the fields are the same, you can use the above method to query and delete useless data.