查询同时包含多个指定用户ID成员的Chat实体

已知实体结构：

• Chat：主键为id（uuid类型）
• ChatMember：联合主键为chatId（关联Chat的id）和userId（用户id，uuid类型），与Chat是一对多关联关系

以下是几种实现查询同时包含多个指定用户ID的Chat实体的方案：

方案一：GROUP BY + HAVING 统计法 #

适用于任意数量的指定用户，扩展性强。以查询包含用户user1和user2的Chat为例：

SELECT c.id
FROM Chat c
INNER JOIN ChatMember cm ON c.id = cm.chatId
WHERE cm.userId IN ('user1', 'user2')
GROUP BY c.id
HAVING COUNT(DISTINCT cm.userId) = 2;

逻辑说明： #

1. 关联Chat和ChatMember表，筛选出包含目标用户的记录
2. 按Chat的id分组，统计每个分组内的不同用户数量
3. 仅保留用户数量等于目标用户数的分组，确保该Chat同时包含所有指定用户

如果需要查询更多用户（如3个），只需在IN子句中添加用户ID，并将COUNT的数值改为对应的数量即可。

方案二：多表连接法 #

适用于指定用户数量较少的场景，逻辑直观。以查询包含user1和user2的Chat为例：

SELECT c.id
FROM Chat c
INNER JOIN ChatMember cm1 ON c.id = cm1.chatId AND cm1.userId = 'user1'
INNER JOIN ChatMember cm2 ON c.id = cm2.chatId AND cm2.userId = 'user2';

逻辑说明： #

通过多次连接ChatMember表，每次指定一个目标用户ID，只有同时满足所有连接条件的Chat才会被查询出来。若要增加目标用户，只需添加对应的INNER JOIN语句即可。

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