我有一个users_conversations表，它包含一个conversation_id和一个user_id，用来告诉哪些用户在同一个会话中。 我想找到任何有USER_ID76和USER_ID146的conversation_id（它们是ID 28，31和32.这些ID我不是不知道，我只是有这两个用户ID
select * from users_conversations where user_id = 146 or user_id = 76 GROUP BY conversation_id
但我得到了
#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'forums.users_conversations.user_id' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
我怎么能那么做？
select * from users_conversations
where conversations_id in (select conversations_id from user_conversations where user_id=76)
AND conversations_id in (select conversations_id from user_conversations where user_id=146)
您可以为这些用户筛选表，GROUP by CONVERSATION_ID并在HAVING子句中设置条件:
select conversation_id
from users_conversations
where user_id in (76, 146)
group by conversation_id
having count(distinct user_id) = 2
如果每个conversation_id没有重复用户，则可以省略distinction。
你的意思是:
select * from users_conversations where user_id = 146 or user_id = 76
为什么要使用组？
