如何让MySQL查询仅匹配数组中全部用户ID而非任意一个？

我有如下MySQL查询语句：

SELECT tbPacientesComunicacao.id 
FROM tbPacientesComunicacao 
WHERE tbPacientesComunicacao.idRefUser = 1 
   AND tbPacientesComunicacao.idRefPaciente = 4
   AND tbPacientesComunicacao.id IN (
     SELECT idRefComunicacao 
     FROM tbPacientesComunicacaoDestinatarios 
     WHERE idRefUser IN (2,8)
   );

当前问题是MySQL会在匹配2或8中任意一个时就返回ID，如何修改才能仅在同时匹配2和8这两个值时才返回ID？

另外，在插入新记录前，我需要检查数据库中是否存在完全匹配指定用户ID组合的通讯记录。例如传入用户ID数组array(5,8)时，需判断是否存在恰好包含这两个用户ID的记录（当前示例中应为不匹配，因为现有记录是5和7），但使用IN语句会因匹配到至少一个值而判定为匹配。请问如何实现仅当数组中所有用户ID都匹配时才返回结果？

现有表结构 #

表tbPacientesComunicacao #

表tbPacientesComunicacaoDestinatarios #

场景1：通讯记录同时包含所有指定用户（允许存在其他用户） #

这种情况只要求目标通讯记录包含所有指定的UserID，不管是否还有其他用户。可以通过分组统计匹配的用户数实现：

SELECT pc.id 
FROM tbPacientesComunicacao pc
JOIN tbPacientesComunicacaoDestinatarios pcd ON pc.id = pcd.idRefComunicacao
WHERE pc.idRefUser = 1 
  AND pc.idRefPaciente = 4
  AND pcd.UserID IN (2, 8) -- 填入需要匹配的用户ID
GROUP BY pc.id
HAVING COUNT(DISTINCT pcd.UserID) = 2; -- 数字要和指定的用户ID数量一致

原理：将主表和关联表连接后，筛选出包含指定用户的记录，按通讯ID分组，统计分组内匹配的不同用户数。如果数量等于指定的用户总数，说明该通讯记录包含了所有指定用户。

场景2：通讯记录恰好只包含指定用户（完全匹配，无其他用户） #

如果需要严格匹配——通讯记录的收件人恰好是指定的用户集合，没有多余的其他用户，可以用以下两种写法：

写法1：分组统计总用户数与匹配用户数 #

SELECT pc.id 
FROM tbPacientesComunicacao pc
JOIN (
  SELECT idRefComunicacao, 
         COUNT(DISTINCT UserID) AS total_users,
         SUM(CASE WHEN UserID IN (5,8) THEN 1 ELSE 0 END) AS matched_users
  FROM tbPacientesComunicacaoDestinatarios
  GROUP BY idRefComunicacao
) pcd_stats ON pc.id = pcd_stats.idRefComunicacao
WHERE pc.idRefUser = 1 
  AND pc.idRefPaciente = 4
  AND pcd_stats.matched_users = 2 -- 匹配用户数等于指定数量
  AND pcd_stats.total_users = 2; -- 总用户数也等于指定数量

写法2：多关联+左联排除其他用户 #

SELECT pc.id 
FROM tbPacientesComunicacao pc
-- 确保每个指定用户都存在
JOIN tbPacientesComunicacaoDestinatarios pcd1 ON pc.id = pcd1.idRefComunicacao AND pcd1.UserID = 5
JOIN tbPacientesComunicacaoDestinatarios pcd2 ON pc.id = pcd2.idRefComunicacao AND pcd2.UserID = 8
-- 确保没有其他用户存在
LEFT JOIN tbPacientesComunicacaoDestinatarios pcd3 ON pc.id = pcd3.idRefComunicacao AND pcd3.UserID NOT IN (5,8)
WHERE pc.idRefUser = 1 
  AND pc.idRefPaciente = 4
  AND pcd3.idRefComunicacao IS NULL;

原理：通过多次JOIN确认所有指定用户都在收件人中，再通过左联非指定用户并判断结果为空，确保没有额外用户存在。

内容的提问来源于stack exchange，提问作者Cezar Duarte