mysql 相同id,MYSQL-获取具有相同ID的多个记录的所有记录

I apologize in advanced if I am not explaining this correctly. I can barely explain it in english terms, let alone in a mysql query.

I am trying to get the list of response_set_ids that have more than 1 record for a question_id.

This is an example of my data:

+----+-----------------+-------------+-----------+

| id | response_set_id | question_id | answer_id |

+----+-----------------+-------------+-----------+

| 1 | 10 | 1 | 4 |

| 2 | 10 | 2 | 5 |

| 3 | 10 | 3 | 6 |

| 4 | 10 | 3 | 7 |

| 5 | 11 | 1 | 8 |

| 6 | 11 | 2 | 9 |

| 7 | 11 | 3 | 10 |

+----+-----------------+-------------+-----------+

I would like to have a query that would return me a list response_set_ids, and in this particular example, I would expect to get returned 10 because that response_set has question_id -> 3 showing up more than once.

Please let me know if you need any further information to help me.

I have tried this:

select response_set_id, count(question_id) from responses group by response_set_id;

But that only gives me the counts of questions per response_set.

Thank you in advanced!

解决方案

The simplest method doesn't use a subquery:

SELECT DISTINCT response_set_id

FROM responses

GROUP BY response_set_id, question_id

HAVING COUNT(*) > 1;

This is one of the very, very few instances where select distinct is used (appropriately) with group by.