php - mysql排序发送和接收的消息

我的数据库中有一个表

SenderId    ReceiverId  Message         DateAndTime
int(11)     int(11)     varchar(200)    DATETIME

我想以对话形式对登录用户的所有消息进行排序。登录的用户之间的消息说UserA和UserB，UserC应该显示如下：

UserA   UserB   Sent
UserA   UserB   Sent
UserB   UserA   Received

UserA   UserC   Sent
UserC   UserA   Received
UserA   UserC   Sent

UserD   UserA   Receied
UserA   UserD   Sent

我已经尝试过此查询，但结果与我预期的不同

SELECT * FROM messages WHERE SenderId like ? or ReceiverId like ? order by senderId, dateAndDate;

最佳答案

这应该可以解决问题。

SELECT SenderId, ReceiverId, Message, DateAndTime, CASE WHEN SenderId=1 THEN ReceiverId Else SenderId END as Optional from messages where SenderId = 1 or ReceiverId=1 order by Optional, DateAndTime desc;

假设Logged UserID为1，请改用占位符。