How to Find Identical Values Across Multiple Rows in MySQL?

MySQL multi-row same value search: complete guide

In relational databases such as MySQL, identifying rows and columns with matching column values ​​is often a critical task. This article explores how to return columns that share the same value in a specific column.

Consider the following data table:

ID	Score
1	95
2	100
3	88
4	100
5	73

To retrieve all scores that appear in both ID 2 and ID 4, one way is to use aggregation and a HAVING clause:

<code class="language-sql">SELECT Score
FROM t
GROUP BY Score
HAVING COUNT(CASE WHEN ID = 2 THEN 1 END) > 0 AND COUNT(CASE WHEN ID = 4 THEN 1 END) > 0;</code>

This query counts the ID 2 and ID 4 rows for each distinct score. Then, return the fraction where both ID 2 and ID 4 have counts greater than zero as the desired result.

Instructions:

• GROUP BY ScoreAggregates rows by Score column.
• HAVING COUNT(CASE WHEN ID = 2 THEN 1 END) > 0 Make sure to only include scores where ID 2 exists.
• HAVING COUNT(CASE WHEN ID = 4 THEN 1 END) > 0Further filter the results to include scores where ID 4 is also present.

By combining these conditions in the HAVING clause, the query effectively identifies scores that appear in both ID 2 and ID 4. This technique can be extended to any number of IDs or columns, providing a general solution for finding matching values ​​in MySQL.

The above is the detailed content of How to Find Identical Values Across Multiple Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!