MySQL - 选择共同值(value)

Question

我正在尝试创建一个查询，但我不知道如何做。

我有三个表:chat、chat_user 和 user

类似于:

 ---------      ------------------------       ------------------
|  chat   |    |        chat_user       |     |       user       |
 ---------      ------------------------       ------------------
| id |  1 |    | id | chat_id | user_id |     | id | external_id | 
 ---- ----      ---- --------- ---------       ---- -------------
               | 1  |    1    |    1    |     |  1 |     111     |
               | 2  |    1    |    2    |     |  2 |     222     |
                ---- --------- ---------       ---- -------------

我需要为两个 user 选择一个 chat.id。在上面的示例中，用户 111 和 222 共享 chat.id 1。我需要的是将 chat.id 1 作为选择结果(作为上面的示例)仅在搜索 external_id 111 和 222 时出现一次。

我在这里找到了一些答案，但它们并没有太大帮助......我总是得到其他 chat.id 值。

如果您需要任何额外的解释，我就在这里回答。

提前致谢!

Answer 1

这实际上是“集内集”查询的示例:您询问给定聊天的用户集是否包含某些用户。我喜欢用聚合和 having 子句来解决此类问题，因为这是一种非常通用的方法。

这里是查询:

select c.*
from chat_user cu join
     user u
     on cu.user_id = u.id join
     chat c
     on cu.chat_id = c.id
group by cu.chat_id
having sum(u.external_id = '111') > 0 and
       sum(u.external_id = '222') > 0

这是通过 chat_user 中的聊天进行聚合。 having 子句中的第一个条件是:“Is user 111 present?”。第二个说:“用户 222 在吗？”仅返回带有两个用户示例的聊天 ID。