mysql - MySQL：选择具有特定字段值的多行

Question

我有一个 users_conversations 表，其中包含一个 conversation_id 和一个 user_id 来告诉哪些用户在同一个对话中。我想找到具有 user_id 76 和 user_id 146 的任何 conversation_id（它们是 ids 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

我怎样才能做到这一点？

标签： mysqlselectgroup-by

Answer 1

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)