MySQL:检索ID，其中恰好2行共享相同的ID但具有不同的用户ID

问题描述

我有一个MySQL表，该表将用户与如下图或sqlfiddle的对话相关联:

I have a MySQL table that associates a user to a conversation that looks like the following image or sqlfiddle:

MySQL表屏幕截图

http://sqlfiddle.com/#!9/b4d329

如您所见，用户1000001和用户1000002都属于2个会话-10和20.

As you can see, user 1000001 and user 1000002 both belong to 2 conversations - 10 and 20.

我需要做的是检索仅用户1000001和用户1000002与之关联的session_id.将被拉出的正确的session_id是10.

What I need to do is retrieve the conversation_id where only users 1000001 and user 1000002 are associated with it. The correct conversation_id that would be pulled is 10.

正如您在session_id 20中看到的那样，第三个用户与之相关联-1000003-因此，即使用户1000001和1000002与之相关联，我也不想拉出该session_id.

As you can see with conversation_id 20, a 3rd user is associated with it - 1000003 - so I don't want to pull that conversation_id, even though users 1000001 and 1000002 are associated with it.

还请注意，只有一个user_id与一个session_id相关联时，这种情况就不会发生.

Also note that the scenario can't happen when only one user_id is associated with a conversation_id.

非常感谢您的帮助！

推荐答案

这是一种高效的方法，完全不使用子查询.您可以使用条件聚合在Having子句中简单地过滤出结果:

Here is a performant approach, using no subqueries at all. You can simply filter out results in Having clause, using conditional aggregation:

SELECT
  conversation_id
FROM assoc_user__conversation
GROUP BY conversation_id
HAVING
  -- all the rows to exists only for 1000001 or 1000002 only
  SUM(user_id IN (1000001, 1000002)) = COUNT(*)

结果

| conversation_id |
| --------------- |
| 10              |

在DB Fiddle上查看

条件聚合的另一种可能的变化是:

Another variation of conditional aggregation possible is:

SELECT
  conversation_id
FROM assoc_user__conversation
GROUP BY conversation_id
HAVING
  -- atleast one row for 1000001 to exists
  SUM(user_id = 1000001) AND
  -- atleast one row for 1000002 to exists
  SUM(user_id = 1000002) AND
  -- no row to exist for other user_id values
  NOT SUM(user_id NOT IN (1000001, 1000002))

这篇关于MySQL:检索ID，其中恰好2行共享相同的ID但具有不同的用户ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！