MySQL - 获取所有用户的最后一条消息

Question

我已经在 stackoverflow 上搜索过了，但是现有的线程对我没有帮助，所以我发布了这个。

我的数据库架构:

user: user_id, displayname
message: id, sender_id, receiver_id, message, dateSent

我想要什么:

显示来自每个用户的最新消息列表，其中 receiver_id = 4

我已经尝试过的:

MYSQL select newest posts from tables query to get last message only from all users

还是不能解决我的问题:(

Answer 1

试试这个:

SELECT
  u1.displayname AS SenderName,
  m.date_sent,
  ...
FROM messages AS m1
INNER JOIN users AS u1 ON m1.sender_id = u1.id
(
   SELECT sender_id, MAX(dateSent) MaxDate
   FROM messages 
   GROUP BY sender_id
   WHERE receiver_id = 4
) AS m2  ON m1.sender_id = m2.sender_id
        AND m1.datesent  = m2.maxdate