我有一个非常基本的聊天应用程序,有两张桌子:Users
和Messages
.这两个表都做了它们在TIN上所说的事情:)当一个用户向另一个用户发送消息时,我在Messages
表上记录了userID
(消息发送者)和userIDReceived
(消息接收者).
有关数据如下:
table : Users
+-------+-----------+
|userID | username |
+-------+-----------+
| 1 | John |
| 2 | Mary |
| 3 | Jeff |
| 4 | Bill |
| 5 | Bob |
+-------+-----------+
table : Messages
+---------+-----------+--------------+------------===----+
|messageID| userID |userIDReceived| created |
+---------+-----------+--------------+-------------------+
| 1 | 1 | 2 |2024-02-01 19:55:37|
| 2 | 1 | 2 |2024-02-01 17:20:10|
| 3 | 2 | 3 |2024-02-01 19:27:18|
| 4 | 3 | 1 |2024-01-16 17:46:57|
| 5 | 3 | 1 |2024-01-26 02:34:44|
+---------+-----------+--------------+-------------------+
我对这些表进行了标准化,以便将用户数据存储在Users
表中,而将消息数据存储在Messages
表中.我try 做的是对特定用户(按userID
)发送和接收的所有消息进行分组,以便仅返回最后发送和/或接收的消息.
下面是我的查询示例,其中userID
1正在try 获取任何其他用户发送给他的最后一条消息:
SELECT DISTINCT 'sent' AS direction, u.userID, u.username, MAX(m.messageID), MAX(m.created), m.message
FROM Messages m
LEFT JOIN Users u
ON m.userIDReceived = u.userID
WHERE m.userID = 1
GROUP BY m.userIDReceived
UNION
SELECT DISTINCT 'received' AS direction, u.userID, u.username, MAX(m.messageID), MAX(m.created), m.message
FROM Messages m
LEFT JOIN Users u
ON m.userID = u.userID
WHERE m.userIDReceived = 1
GROUP BY m.userID;
查询可以工作,但是返回的"Message"值总是不正确的--它似乎总是显示在两个用户之间发送的第一条消息.我似乎无法将MAX()
应用于Message列(因为它是一个文本字段,因此try 对其进行排序是毫无意义的),或者将ORDER BY
应用于返回的日期以显示正确的消息值.
I want my result to look like this:
+---------+-----------+--------------+------------===----+---------+
|messageID| userID |username| created |message |
+---------+-----------+--------------+-------------------+---------+
|sent | 1 | 2 |2024-02-01 19:55:37|Hey there|
|sent | 1 | 2 |2024-02-01 17:20:10|Hi |
|sent | 2 | 3 |2024-02-01 19:27:18|How |
|received | 3 | 1 |2024-01-16 17:46:57|Yeah good|
|received | 3 | 1 |2024-01-26 02:34:44|Cool |
+---------+-----------+--------------+-------------------+---------+
请注意,实际上并不需要返回列messageID
;我只是想用它来解决这个查询问题.
此外,服务器版本为5.7.44-cll-lve-mySQL社区服务器-(GPL)
有什么主意吗?