我正在开发一个聊天系统,会话列表必须显示两件事：
>发送消息的人的最后消息(我是当前用户或其他用户)
>其他用户的名称
我遇到问题的部分是第二点.当前查询显示每个会话的最后一条消息,但在我(当前用户)发送最后一条消息的情况下,而不是我的名字,它应该显示另一个用户的名字.
SELECT SQL_CALC_FOUND_ROWS
u.id_user AS id,
i.id_user_from,
i.id_user_to,
u.name AS name,
UNIX_TIMESTAMP(i.date_msg) AS date_msg,
i.message AS msg
FROM inbox AS i
INNER JOIN user AS u ON (u.id_user = i.id_user_from OR u.id_user = i.id_user_to)
WHERE id_msg IN
(SELECT MAX(id_msg) AS id FROM
(
SELECT id_msg, id_user_from AS id_with
FROM inbox
WHERE id_user_to = 1
UNION ALL
SELECT id_msg, id_user_to AS id_with
FROM inbox
WHERE id_user_from = 1) AS t
GROUP BY id_with
)
ORDER BY i.id_msg DESC
在这个例子中,我是Andufo(id_user = 1).如果它有帮助,这是一个sqlfiddle link.谢谢！
解决方法:
可能这有帮助
SELECT SQL_CALC_FOUND_ROWS
u.id_user AS id,
i.id_user_from,
i.id_user_to,
u.name AS name,
UNIX_TIMESTAMP(i.date_msg) AS date_msg,
i.message AS msg
FROM inbox AS i
INNER JOIN user AS u ON u.id_user = IF(i.id_user_from = 1 /*me*/, i.id_user_to, i.id_user_from)
WHERE id_msg IN
(SELECT MAX(id_msg) AS id FROM
(
SELECT id_msg, id_user_from AS id_with
FROM inbox
WHERE id_user_to = 1
UNION ALL
SELECT id_msg, id_user_to AS id_with
FROM inbox
WHERE id_user_from = 1) AS t
GROUP BY id_with
)
ORDER BY i.id_msg DESC