How to Find Duplicate Score Values Across Specific IDs in MySQL?

Tricks for retrieving duplicate column values ​​in MySQL

In a MySQL database, you may need to retrieve those rows that share the same value in a specific column. Consider the following table, where the "Score" column contains values ​​associated with different "IDs":

ID	分数
1	95
2	100
3	88
4	100
5	73

Suppose you want to retrieve the scores that occur in two rows with "ID" equal to 2 and 4.

Solution:

To achieve this using MySQL, you can use the following query:

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

This query performs the following steps:

1. GROUP BY: It groups rows based on the "Score" column.
2. *Count (COUNT()):** It counts the number of rows in each group, ensuring that only duplicate scores are returned.
3. Conditional Count (SUM(CASE WHEN ... END)): It counts the number of rows with ID 2 and ID 4 in each group respectively.
4. HAVING clause: The HAVING clause ensures that only scores with score counts greater than 1 are returned, and scores with ID 2 and ID 4 are both greater than 0.

In this case the result would be:

<code>分数
100</code>

This query effectively retrieves the score values ​​that occur in both rows with "ID" equal to 2 and 4. The improved query expresses filter conditions more clearly and avoids potential logic errors.

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