使用MySQL在聊天消息数据库中获得通用连接-GettingincommonconnectionsinachatmessagesdatabaseusingMySQL

I need to perform a complex query on a fairly simple table that stores chat messages. The table has the following columns:

我需要在一个存储聊天消息的相当简单的表上执行复杂查询。该表包含以下列：

|message_id|from|to|body|

The from and to fields contains user IDs which are just encoded as integers. In the app, there is a conversation between user A and user B if user A has sent a message to user B and vice versa. My problem now is that for user A, I need to query all persons that A has in common with B regarding conversations.

from和to字段包含仅编码为整数的用户ID。在应用程序中，如果用户A已向用户B发送消息，则用户A和用户B之间存在对话，反之亦然。我现在的问题是，对于用户A，我需要查询A与B有关的所有人的对话。

So for example, if user A has a conversation with user B and D and user B has a conversation with user C and I need to query all common users of A and B, the result set will only consist of C. D is not included since user B has no conversation with user D.

因此，例如，如果用户A与用户B和D进行对话，并且用户B与用户C进行对话，并且我需要查询A和B的所有普通用户，则结果集将仅包括C.D不包括在内因为用户B没有与用户D交谈。

I have experience with MySQL but I have no clue where to start here. I prefer to do this (complex) query in one run but I wonder if there exists an efficient algorithm with multiple queries to do this task.

我有MySQL的经验，但我不知道从哪里开始。我更喜欢在一次运行中执行此（复杂）查询，但我想知道是否存在具有多个查询的有效算法来执行此任务。

Any help would be greatly appreciated!

任何帮助将不胜感激！

1 个解决方案

SELECT to FROM Test WHERE from = 'A' AND to IN (SELECT to FROM Test WHERE from = 'B')