在 SQL Server 中查询对话中用户的精确匹配

我有一个对话表和一个用户对话表。

CONVERSATION
Id, Subject, Type

USERCONVERSATION
Id, UserId, ConversationId

我需要根据 UserId 列表执行 SQL 查询。因此，如果同一个 ConversationId 有三个 UserId，我需要执行一个查询，如果我提供相同的三个 userId，它将返回它们完全匹配的 ConversationId。

假设同一用户不能两次出现在 UserConversation 中：

SELECT ConversationID
FROM UserConversation
GROUP BY ConversationID
HAVING
   Count(UserID) = 3 -- this isn't necessary but might improve performance
   AND Sum(CASE WHEN UserID IN (1, 2, 3) THEN 1 ELSE 0 END) = 3

这也有效：

SELECT ConversationID
FROM
   UserConversation UC
   LEFT JOIN (
      SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   ) U (UserID) ON UC.UserID = U.UserID
GROUP BY ConversationID
HAVING
   Count(U.UserID) = 3
   AND Count(UC.UserID) = 3

如果您发现这些查询中的任何一个查询的性能都很差，那么两步方法可能会有所帮助：首先查找包含以下内容的所有对话at least所需的各方，然后从该组中排除包含任何其他方的各方。索引当然会产生很大的影响。

从 UserConversation 中删除 ID 列将通过每页获取更多行来提高性能，从而每次读取更多数据（大约多 50%！）。如果你的Id列不仅是PK而且是聚集索引，那么立即去将聚集索引更改为ConversationId, UserId（或反之亦然，取决于最常见的用法）！

如果您需要性能方面的帮助，请发表评论，我会尽力帮助您。

附：这是另一个疯狂的想法，但它可能不会表现得那么好（尽管有时事情会让你感到惊讶）：

SELECT
   Coalesce(C.ConversationID, UC.ConversationID) ConversationID
   -- Or could be Min(C.ConversationID)
FROM
   Conversation C
   CROSS JOIN (
      SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   ) U (UserID)
   FULL JOIN UserConversation UC
      ON C.ConversationID = UC.ConversationID
      AND U.UserID = UC.UserID
GROUP BY Coalesce(C.ConversationID, UC.ConversationID)
HAVING Count(*) = Count(U.UserID)