mysql 条件相同id不同,MySQL：检索ID，其中恰好2行共享相同的ID但具有不同的用户ID...

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

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

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.

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.

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

Thank you so much for your help!

解决方案

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(*)

Result

| conversation_id |

| --------------- |

| 10 |

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))