mysql根据索引查询id_mysql-根据UserId检索特定的GroupId

在这种情况下,我们想检索特定的GroupId号.例如,只有3个用户的6个用户说该特定组的UserId为2、1、3

这是我尝试执行的查询

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3) AND userid IN (1, 2, 3);

以下是上述查询的详细信息,这些细节已细分

SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3

group_id

5

6

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3)

group_id

5

5

5

6

6

6

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3) AND userid IN (1, 2, 3)

group_id

5

5

6

6

6

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3) AND userid=1 AND userid=2 and userid = 3

group_id

NULL

预期结果：

group_id

6

由于只有一个group_id = 6的组具有3个用户,而唯一的userid = 1、2、3

因此必须在最终结果中检索group_id = 6！