查询 SQL Server 对话中用户的精确匹配 [英] Query for exact match of users in a conversation in SQL Server

问题描述

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

I have a conversation table, and a user conversation table.

CONVERSATION
Id, Subject, Type

USERCONVERSATION
Id, UserId, ConversationId

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

I need to do a SQL Query based on a list of UserIds. So, if I have three UserIds for the same ConversationId, I need to perform a query where if I provide the same three userIds, it will return the ConversationId where they match exactly.

推荐答案

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

Assuming the same user can't be in a UserConversation twice:

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

如果您发现这些查询中的任何一个的性能都很差，那么两步方法可能会有所帮助:首先找到包含至少所需方的所有对话，然后从该集合中排除那些包含任何其他人.索引当然会有很大的不同.

If you find that performance is poor with either of these queries then a two-step method could help: First find all conversations containing at least the desired parties, then from that set exclude those that contain any others. Indexes of course will make a big difference.

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

Getting rid of the ID column from UserConversation will improve performance by getting more rows per page, thus more data per read (about 50% more!). If your Id column is not only the PK but also the clustered index, then immediately go change the clustered index to ConversationId, UserId (or vice versa, depending on the most common usage)!

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

If you need help with performance post a comment and I'll try to help you.

附言这是另一个疯狂的想法，但它可能效果不佳(尽管有时会让您感到惊讶):

P.S. Here's another wild idea but it may not perform as well (though things can surprise you sometimes):

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)

这篇关于查询 SQL Server 对话中用户的精确匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！