我需要一个sql查询，它可以让我得到所有用户朋友的最后消息的详细信息。我的sql数据库中的字段是:
message_id (primary key)
sender (username of the sender)
message_content (text of the message)
user_id (user id of the app user)
friend_id (user id of the app user's friends)
message_time (time of the message receive on server)
receiver (user id of the receiver)
编辑:到目前为止，我认为我最接近的尝试是
Select * from user_chat_messages where message_id on (Select distinct message_id from user_chat_messages order by message_time limit 1,0);
Rest所有我尝试的其他查询都是完全失败的:(
这是一个非常常见的mysql问题，您需要连接表本身以获得每个唯一用户的最小/最大结果集。
试试这样写:
SELECT t1.* FROM table t1
JOIN (
SELECT MAX(message_time) AS 'time', friend_id, user_id
FROM table GROUP BY friend_id, user_id
) t2 ON (t1.message_time = t2.time AND t1.friend_id = t2.friend_id AND t1.user_id = t2.user_id)
实质上，子查询找到按friend_id分组的最新时间，然后将其连接回主表，以便它只从第一个表中提取具有最新时间的记录。从那里，您可以添加WHERE语句，仅显示来自特定用户的最新消息—在子查询中添加条件实际上会提高性能
SELECT t1.* FROM table t1
JOIN (
SELECT MAX(message_time) AS 'time', friend_id, user_id
FROM table
WHERE user_id=? GROUP BY friend_id
) t2 ON (t1.message_time = t2.time AND t1.friend_id = t2.friend_id AND t1.user_id = t2.user_id)