問題描述

我有一個對話表和一個用戶對話表.

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 對話中用戶的精確匹配的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！