有没有更好的方法来编写这些查询?(里面有坏的SQL消息传递模式..)

Question

我继承了一个使用 SQL Server （2008 R2） 的系统，其中包含两个涵盖系统消息传递的表。一条消息具有一对多关系，可以发送给多个用户。

主表"消息传递"如下所示：

Id (PK, auto increment)
Subject
MessageBody
CreatorEmployeeId
ConversationId
IsConversationStarter (bool)

辅助表"消息详细信息"如下所示：

ID (PK, auto increment)
MessageId (FK to Messaging PK)
RecipientEmployeeId
ConversationId
IsRead (bool)

如果不清楚，则会将新消息放入消息传递表中，其中包含创建消息的人员的员工 ID，并创建新的对话 ID。然后，X 行将插入到消息详细信息中，每个收件人一个行，并引用回消息传递表并使用相同的对话 ID。

任何新答复看起来都一样，回复邮件进入消息传递表，收件人详细信息使用相同的对话 ID 进入消息传递详细信息，以便您可以按一个数字拉取整个邮件线程。

当您尝试获取已创建或已发送给您的所有未读邮件的计数时，这会变得草率。下面是一个现有查询，用于获取系统中其他用户发送给登录用户的所有未读消息，这些用户最初在其中创建了消息：

SELECT * FROM dbo.Messaging m
INNER JOIN dbo.MessagingDetails m1 on m.Id = m1.MessageId
INNER JOIN dbo.MessagingDetails m2 on m1.ConversationId = m2.ConversationId
WHERE m1.RecipientEmployeeId = @employeeId
AND m.IsConversationStarter = 1
AND m.ConversationId = m1.ConversationId
AND m2.IsRead = 0 
AND m2.RecipientEmployeeId == @employeeId

然后，要查找员工创建但有未读回复的所有邮件，您必须执行几乎相同的查询，将 WHERE 子句的第一部分替换为：

m.CreatorEmployeeId = @employeeId

我讨厌内部两次加入同一张桌子，一次是自己。鉴于上面概述的架构，是否有更好的方法来编写这些查询？

Answer 1

对于"员工发送的有未发送回复的消息"，我认为这解决了问题：

with EmployeeCreatedMessages as (
     select distinct MessageId
     from Messaging
     where m.CreatorEmployeeId = @employeeId
    )
select distinct MessageId
from MessageDetails md
where md.MessageId in (select MessageId from EmployeeCreatedMessages) and
      md.IsRead = false

我不确定您想要有关该消息的哪些信息。 这只给出消息 ID。

相同的方法适用于其他人发送给用户的所有消息：

with OtherCreatedMessages as (
     select distinct MessageId
     from Messaging
     where m.CreatorEmployeeId <> @employeeId
    )
select distinct MessageId
from MessageDetails md
where md.MessageId in (select MessageId from OtherCreatedMessages) and
      md.IsRead = false and
      md.RecipientEmployeeId = @employeeid

您的描述和问题未提及转化 ID。 这相关吗？

Answer 2

为了摆脱第二个连接到dbo.MessagingDetails，这个查询应该等效于你的。

SELECT * FROM dbo.Messaging m
INNER JOIN dbo.MessagingDetails m1 on m.Id = m1.MessageId
WHERE m1.RecipientEmployeeId = @employeeId
AND m.IsConversationStarter = 1
AND m.ConversationId = m1.ConversationId
AND EXISTS(SELECT * FROM dbo.MessagingDetails m2
    WHERE m1.ConversationId = m2.ConversationId
    AND m2.IsRead = 0 AND m2.RecipientEmployeeId == @employeeId)

我不认为它会更有效率，你必须检查执行计划。