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

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