时间戳

首页 > 解决方案 > 对话中的最新消息 - 组合了多个 SQL 语句

sql - 对话中的最新消息 - 组合了多个 SQL 语句

问题描述

我正在开发一个收件箱应用程序，并有一个名为 Messages 的表，其中包含用户之间发送的所有消息。我们正在为此设置会话视图。如果您回复一条消息，那么第一条消息的消息 ID 将成为所有后续回复的 parentMessageID。它有几个字段，但是为此我真的只关心其中的 3 个。带有示例数据的基本设置是：

MessageID (Guid)   |   Date     |  ParentMessageID (Guid)   | recipientID
  00000000           07/11/18         NULL                       1
  11111111           07/12/18         00000000                   2
  22222222           07/10/18         NULL                       2
  33333333           07/11/18         22222222                   1
  44444444           07/01/18         NULL                       2

现在我有以下内容：

Select * from messages where recipientID = X AND ParentMessageID is null Order by Date DESC

这些结果变成了一个列表。在此之后，我遍历消息列表并检查它是否有任何子消息

Select top 1 * from messages where parentMessageID = X order by date desc

如果这产生了结果，那么我只需将列表中的父消息替换为我刚刚找到的子消息。这很好用……但我需要能够将其转换为一个用于缓存目的的 SQL 查询，以便我可以使用 .Skip 和 .Take 进行分页。

我尝试了不同的连接和分组语句，但没有运气，而且一旦我弄清楚了，我必须确保它可以与 LINQ 一起使用。我们使用的是 SQL Server 17。

如果使用上述数据作为参考，所需的结果集将是收件人 ID 2：

MessageID (Guid)   |   Date     |  ParentMessageID (Guid)   | recipientID
  11111111           07/12/18         00000000                   2
  44444444           07/01/18         NULL                       2

对于收件人 1：

MessageID (Guid)   |   Date       |  ParentMessageID (Guid)  | recipientID
  00000000           07/11/18 2:00        NULL                       1
  33333333           07/11/18 1:20        22222222                   1

标签： sqlsql-serverlinq

解决方案

在弄清楚如何只使用最新的子消息之后，我想出了这个解决方案：

DECLARE @X int = 2;

WITH
  MsgChildNumbering (MsgID, [Date], ParentMsgID, RcptID, ChildNo) AS (
    SELECT MessageID, [Date], ParentMessageID, RecipientID
      , ROW_NUMBER() OVER (PARTITION BY ParentMessageID ORDER BY [Date] DESC)
    FROM Messages 
  ),
  CTE_Msg (MsgID, [Date], ParentMsgID, RcptID, RootMsgID, LevelNo) AS (
    SELECT MsgID, [Date], ParentMsgID, RcptID, MsgID, 1
    FROM MsgChildNumbering WHERE ParentMsgID IS NULL AND RcptID = @X
    UNION ALL
    SELECT c.MsgID, c.[Date], c.ParentMsgID, c.RcptID, p.RootMsgID, 1+p.LevelNo
    FROM MsgChildNumbering AS c -- child messages with numbering
      INNER JOIN CTE_Msg AS p -- parent messages from last iteration
        ON c.ParentMsgID = p.MsgID
    WHERE c.ChildNo = 1
  ),
  MsgNumbering (MsgID, [Date], ParentMsgID, RcptID, MsgNo) AS (
    SELECT MsgID, [Date], ParentMsgID, RcptID
      , ROW_NUMBER() OVER (PARTITION BY RootMsgID ORDER BY LevelNo DESC)
    FROM CTE_Msg
  )
SELECT MsgID, [Date], ParentMsgID, RcptID
FROM MsgNumbering WHERE MsgNo = 1;
  • CTE MsgChildNumbering用于创建子代的编号，以便能够为每个父代仅添加最新的子代。
  • CTE_Msg是递归部分，它从与接收者对话的所有初始消息开始，@X然后依次添加子消息ChildNo = 1（每个父母只有最新的消息）。
  • 最后，CTE MsgNumbering在会话中添加消息的反向编号（相同RootMsgID），LevelNo以便能够找到每个会话的最新消息。

推荐阅读