MYSQL-获取具有相同ID的多于1条记录的所有记录 [英] MYSQL - Get all records that have more than 1 record for the same id

问题描述

如果无法正确解释，我深表歉意.我几乎不能用英语来解释它，更不用说在mysql查询中了.

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.

我正在尝试获取response_set_ids的列表，其中question_id的记录超过1条.

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

这是我的数据示例:

+----+-----------------+-------------+-----------+
| 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 |
+----+-----------------+-------------+-----------+

我希望有一个查询，该查询将向我返回一个列表response_set_ids，在此特定示例中，我希望得到返回的10，因为该response_set的question_id -> 3出现了多次.

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.

我已经尝试过了: select response_set_id, count(question_id) from responses group by response_set_id;

但是，这只给了我每个response_set的问题计数.

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

谢谢你！

推荐答案

最简单的方法不使用子查询:

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;

这是极少数将select distinct与group by一起使用的实例之一.

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

这篇关于MYSQL-获取具有相同ID的多于1条记录的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！