sql - 消息传递功能 创建 Sql 查询和数据库 View

Question

鉴于以下情况:

我需要能够获得给定用户参与的所有线程的列表，首先按最近的消息排序，只显示最新的消息(每个线程 1 条消息)

这是上述的 SQL 查询:

SELECT
    Message.MessageId,
    Message.CreateDate,
    Message.Body,
    Login.Username,
    (SELECT MessageReadState.ReadDate
        FROM MessageReadState
        WHERE MessageReadState.MessageId = Message.MessageId
            AND MessageReadState.LoginId = 2) AS ReadState
FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
WHERE Message.MessageId IN (
    SELECT Max(Message.MessageId)
    FROM MessageThreadParticipant
    INNER JOIN Message
        ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
    WHERE MessageThreadParticipant.LoginId=2
    GROUP BY MessageThreadParticipant.MessageThreadId
)
ORDER BY Message.CreateDate DESC;

它的工作方式是这样的。一个 Loginid 1 发送一个 LoginId 2 消息:

1. 创建一个新的线程，并在MessageThread中插入一条记录

2. Message 表中添加了一条新记录，带有上面的 treadId

3. 在 MessageThreadParticipant 中插入两条记录(Sender LoginId 和 Recipient LogiId)

   当用户打开他/她的消息列表时，MessageReadState 将更新消息的 ReadDate。

问题:我想创建一个 View ，我可以在其中简单地过滤 LoginId(我会使用 LINQ)。但是我不能用上面的查询来做到这一点(因为我需要在 sql 语句中的某个地方传递 loginId)。无论如何修改上面的 SQL 查询，让我有一个 View ？这是什么？

编辑:我想我有它，但我不确定这是否是最好/最有效的解决方案:

SELECT
    Message.MessageId,
    Message.MessageThreadId,
    Message.SenderLoginId,
    Mtp.LoginId,
    Login.Username,
    Message.CreateDate,
    Message.Body,
    (SELECT MessageReadState.ReadDate
        FROM MessageReadState
        WHERE MessageReadState.MessageId = Message.MessageId
            AND MessageReadState.LoginId = Mtp.LoginID) AS ReadState
FROM Message 
    INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
    INNER JOIN MessageThreadParticipant Mtp ON Mtp.MessageThreadId = Message.MessageThreadId
WHERE Message.MessageId IN (
    SELECT Max(Message.MessageId)
    FROM MessageThreadParticipant
    INNER JOIN Message
        ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
    WHERE MessageThreadParticipant.LoginId=Login.LoginID
    GROUP BY MessageThreadParticipant.MessageThreadId
)
ORDER BY Message.CreateDate DESC;

Answer 1

这是否符合您的要求？

SELECT
  Login.LoginID,
  Login.Username,
  Message.MessageThreadID,
  Message.MessageId,
  Message.CreateDate,
  Message.Body,
  Sender.LoginID                     AS SenderLoginID,
  Sender.Username                    AS SenderUsername,
  MessageReadState.ReadDate          AS RecipientReadDate
FROM
  Login
INNER JOIN
  MessageThreadParticipant
    ON MessageThreadParticipant.LoginId = Login.LoginID
-- This gives all threads every LoginID has ever participated in

CROSS APPLY
  (SELECT TOP 1 * FROM Message WHERE ThreadId = MessageThreadParticipant.MessageThreadId ORDER BY CreateDate DESC) AS Message
-- This gives the newest message for each of those threads.
-- The Login.LoginID could be either the Sender or Recipient

INNER JOIN
  Login AS [Sender]
    ON Sender.LoginID = Message.SenderLoginID
LEFT JOIN
  MessageReadState
    ON  MessageReadState.MessageID = Message.MessageID
    AND MessageReadState.LoginId <> Sender.LoginID
-- This gets the Sender's details, and tries to get whether the recipient read the message
-- It assumes the only MessageReadState entries are for the Sender and Recipient.

ORDER BY
  Message.CreateDate DESC;