如何从pivot关联表中筛选匹配精确值的指定会话字段

• conversations：存储会话的基础信息
• users：存储用户的相关信息
• conversation_users：关联中间表，存储用户与会话的所属对应关系

你需要筛选的是仅包含用户1、3、4、5四个成员，且四个成员全部在列的专属会话，对应conversation_id=12的关联行，可通过分组聚合校验成员匹配度实现，在你现有查询的基础上修改后的SQL如下：

方法1：先查询符合要求的会话ID #

SELECT conversation_id
FROM conversations_users 
WHERE conversation_id IN (1,12)
GROUP BY conversation_id
HAVING 
  -- 校验会话总成员数为4
  COUNT(DISTINCT user_id) = 4
  -- 校验不存在1、3、4、5之外的成员
  AND SUM(CASE WHEN user_id NOT IN (1,3,4,5) THEN 1 ELSE 0 END) = 0
  -- 校验1、3、4、5四个用户都在会话内
  AND SUM(CASE WHEN user_id IN (1,3,4,5) THEN 1 ELSE 0 END) = 4

执行后会返回你需要的目标会话ID 12。

方法2：直接查询目标会话的所有关联行 #

如果需要直接拿到红框标注的所有目标行，嵌套子查询即可：

SELECT * FROM conversations_users
WHERE conversation_id = (
    SELECT conversation_id
    FROM conversations_users 
    WHERE conversation_id IN (1,12)
    GROUP BY conversation_id
    HAVING 
      COUNT(DISTINCT user_id) = 4
      AND SUM(CASE WHEN user_id NOT IN (1,3,4,5) THEN 1 ELSE 0 END) = 0
      AND SUM(CASE WHEN user_id IN (1,3,4,5) THEN 1 ELSE 0 END) = 4
)

1. 先将查询范围限定在你已经查到的用户1所属的会话1、12中，缩小检索范围提升效率
2. 按会话ID分组后通过三个条件联合校验：成员总数正确、无额外用户、所有目标用户都在会话内，避免漏匹配或错匹配的情况

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