如果我们假设每个会话至少有两个参与者，那么我们可以删除其他人在场的所有会话，只留下这两个用户在场的会话。
注：我们需要上述假设，因为在某些情况下 1000001 是唯一的参与者，我们不想展示这些对话。

select conversation_id
from table
where conversation_id not in (
    select conversation_id 
    from table 
    where user_id not in (1000001, 1000002))

编辑：
如果可能只有一个用户id与一个会话id相关联，我们也可以删除这些会话。

select conversation_id
from table
where conversation_id not in (
    select conversation_id 
    from table 
    where user_id not in (1000001, 1000002))
group by conversation_id
having count(*) = 2

假设同一个用户id不会在同一个会话中出现两次。

这是一种性能方法，完全不使用子查询。您只需在中筛选出结果即可 Having 子句，使用条件聚合：

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视图
条件聚合的另一个可能变体是：

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

我还没有测试过这个代码，但是我很确定它在逻辑上是合理的。你没说table的名字，所以我就叫它table。

select conversation_id from (
  select conversation_id, count(*) as count, t1.user_id as user_1, t2.user_id as user_2 
  from table t1
  join table t2 on (t1.conversation_id = t2.conversation_id and t1.user_id != t2.user_id)
  where t1.user_id = 1000001 and
  t2.user_id = 1000002
) as inner_table
where inner_table.count = 2