在每次对话中获取最新消息

Question

我有一个消息表：CREATE TABLE OneToOneMessages (    MessageID int NOT NULL AUTO_INCREMENT,    AuthorID int NOT NULL,    RecipientID int NOT NULL,    MessageText TEXT,    SentTime DATETIME,    PRIMARY KEY (MessageID),    FOREIGN KEY (AuthorID) REFERENCES Users(ID),    FOREIGN KEY (RecipientID) REFERENCES Users(ID));我需要按顺序从每个对话中获取最新消息。我在网上找到了一些查询，但我不知道它们是如何工作的，也不确定它们是否能完成工作。我想我可以一个接一个，从每个对话中获取最后一条消息，然后按发送时间对它们进行排序。如果我可以使用一个查询来获取所有信息，那将是最好的。

Answer 1

您可以使用NOT EXISTS具有相关子查询的条件来过滤表。该条件确保不存在具有相同发件人/收件人或收件人/发件人元组和更长发送时间的其他记录：

SELECT t.*

FROM OneToOneMessages t

WHERE NOT EXISTS (

    SELECT 1

    FROM OneToOneMessages t1

    WHERE 

        (

            t.AuthorID = t1.AuthorID AND t.RecipientID = t1.RecipientID)

            OR t.RecipientID = t1.AuthorID AND t.AuthorID = t1.RecipientID

        )

        AND t1.SentTime > t.SentTime

)

如果您使用的是 MySQL 8.0，则可以使用窗口函数更有效地完成此操作ROW_NUMBER()：

SELECT *

FROM (

    SELECT

        t.*,

        ROW_NUMBER() OVER(

            PARTITION BY LEAST(AuthorID, RecipientID), GREATEST(AuthorID, RecipientID) 

            ORDER BY SentTime DESC

        ) rn

    FROM OneToOneMessages t

)x 

WHERE rn = 1

内部查询通过在具有相同发件人/收件人或收件人/发件人元组的记录组中降低发送时间来对记录进行排名。外部查询仅过滤每个组中的顶部记录。