MySQL:检索ID，其中x个用户ID与其关联

Question

非常感谢您的帮助。

我有一个MySQL表，看起来像下面的DB Fiddle:

https://www.db-fiddle.com/f/xa2Dt9cAPhiMfHcEv8Lifo/0

我需要做的是检索conversation_id，在那里我可以指定与之相关联的user_id，并检索与这些user_id完全相关联的conversation_id。

第一个例子：假设我想得到conversation_id，其中只有用户1000001和1000002与之关联。正确的conversation_id是10。

第二个例子：假设我想得到conversation_id，其中只有用户1000001、1000002和1000003与之关联。正确的conversation_id是20。

等等

还要注意，当只有一个user_id与conversation_id关联时，这种情况就不会发生。

我在这里发布了我的第一个问题，但后来意识到我需要能够提取一个conversation_id，其中有2个或更多(最多16个(指定的user_id与之关联：MySQL：检索id，其中正好有2行共享相同的id，但有不同的userID

我收到的答案之一是，通过在in子句中指定user_id，然后将用户总数放入COUNT(DISTINCT user_id(子句，它可以适用于2个或多个user_id。然而，就性能而言，它似乎是最慢的答案，并且似乎只有在user_id_2密钥是可能的密钥(包含conversation_id和user_id(时才在EXPLAIN中使用conversation_id密钥。

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(*) AND 
-- number of unique user_id is 2
COUNT(DISTINCT user_id) = 2

非常感谢你的帮助！

Answer 1

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table 
( id SERIAL PRIMARY KEY
, user_id int  NOT NULL
, conversation_id int NOT NULL
, UNIQUE(user_id,conversation_id)
, INDEX(conversation_id,user_id)  
);
INSERT INTO my_table (id, user_id, conversation_id) VALUES
(1, 1, 10),
(2, 2, 10),
(3, 1, 20),
(4, 2, 20),
(5, 3, 20),
(6, 1, 30),
(7, 2, 30),
(8, 3, 30),
(9, 4, 30);
SELECT x.conversation_id
FROM my_table x
LEFT
JOIN my_table y
ON y.conversation_id = x.conversation_id
AND y.user_id NOT IN(1,2)
WHERE x.user_id IN (1,2)
AND y.id IS NULL
GROUP
BY x.conversation_id
HAVING COUNT(*) = 2;
+-----------------+
| conversation_id |
+-----------------+
|              10 |
+-----------------+