时间戳

首页 > 解决方案 > 在每次对话中获取最新消息

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

问题描述

我有一个消息表：

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)
);

我需要按顺序从每个对话中获取最新消息。我在网上找到了一些查询，但我不知道它们是如何工作的，也不确定它们是否能完成工作。

我想我可以一个一个地去，从每个对话中获取最后一条消息，然后按发送时间对它们进行排序。如果我可以使用一个查询来获取所有信息，那将是最好的。

标签： phpmysqlsqlandroid-studio

解决方案

您可以使用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

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

推荐阅读