基于SQL判断群聊消息是否需新建group_id的技术咨询

我正在开发一个基于群组的聊天系统，要求消息仅发送给同一群组内的成员。例如：

• Sarah、James和Chris属于同一群组
• Sarah、James、Chris和Paul不属于同一群组
• Sarah和James也不属于同一群组

现有数据模型：

Table A（用户-群组关联表） #

user_id|group_id
   1   |   1
   2   |   1
   1   |   2
   2   |   2
   3   |   2

Table B（存储过程输入表，代表单条群聊消息会话） #

sender_id|recipient_id|text 
    1    |     2      | Hey 
    1    |     3      | Hey 

Table B中sender_id和text一致，说明user_id 1、2、3属于同一个群聊会话，且这个会话不能是仅包含1和2，或仅包含1和3的群组。

当前需要解决的问题：无法预先知晓消息对应的group_id，需动态判断：

1. 是否存在Table A中成员集合完全匹配Table B内所有用户的group_id？存在则使用该现有群组
2. 若不存在完全匹配的群组，则需要创建新的group_id

消息最终存储表结构：

group_id|sender_id|message_id|text
   ?    |     1   |   ...    |Hey

请问如何基于Table A和Table B判断是否需要创建新的group_id？

核心思路是：先提取当前聊天会话的完整用户集合，再在用户-群组关联表中查找是否存在成员集合与其完全一致的群组。

1. 提取当前会话的用户集合 #

首先合并Table B中的sender_id和recipient_id，得到本次聊天的所有用户：

WITH chat_users AS (
    SELECT sender_id AS user_id FROM Table B
    UNION
    SELECT recipient_id AS user_id FROM Table B
)
SELECT user_id FROM chat_users;

2. 查找匹配的群组ID #

通过SQL筛选出同时满足以下两个条件的group_id：

• 该群组的所有成员都属于当前会话的用户集合
• 该群组的成员数量等于当前会话的用户总数

WITH chat_users AS (
    SELECT sender_id AS user_id FROM Table B
    UNION
    SELECT recipient_id AS user_id FROM Table B
),
chat_user_count AS (
    SELECT COUNT(*) AS total_users FROM chat_users
)
SELECT a.group_id
FROM Table A a
JOIN chat_users cu ON a.user_id = cu.user_id
GROUP BY a.group_id
HAVING COUNT(DISTINCT a.user_id) = (SELECT total_users FROM chat_user_count)
AND NOT EXISTS (
    SELECT 1
    FROM Table A a2
    WHERE a2.group_id = a.group_id
    AND a2.user_id NOT IN (SELECT user_id FROM chat_users)
);

3. 判断是否需要创建新群组 #

• 若上述查询返回非空结果，说明存在完全匹配的现有群组，直接使用返回的group_id即可
• 若查询返回空结果，说明没有完全匹配的群组，需要创建新的group_id，将当前会话的所有用户与新group_id关联后插入Table A，再用该group_id存储消息

示例验证 #

针对你提供的测试数据：

• 当前会话用户集合是{1,2,3}，总数为3
• group_id=1的成员是{1,2}，数量为2，不满足总数匹配
• group_id=2的成员是{1,2,3}，数量和成员集合都完全匹配，因此直接使用group_id=2

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