How to Find Matching Column Values Across Multiple Rows in MySQL?

Identifies matching column values ​​in MySQL rows

MySQL provides a versatile mechanism for retrieving data from tables, including isolating rows based on common column values. This article explores how to return those rows that have the same value in a specific column.

Problem Statement:

Consider the following table:

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

The task is to retrieve only the scores corresponding to ID 2 and 4, in this case 100.

Solution:

MySQL provides several ways to accomplish this task. The most appropriate method depends on the specific requirements.

Use HAVING clause for aggregation:

For this situation, the combination of aggregates and HAVING clauses provides an elegant solution:

<code class="language-sql">SELECT score
FROM t
GROUP BY score
HAVING SUM(id = 2) > 0 AND  -- 包含id = 2
       SUM(id = 4) > 0     -- 包含id = 4</code>

This query groups rows by score, counting the occurrences of ID 2 and ID 4 in each score. The HAVING clause ensures that only scores that exist in both ID 2 and ID 4 are returned.

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