我有一个要求，我必须根据未读消息和最新对话从聊天表中对用户进行排序。
我的表格结构如下：
id|from_member_id|to_member_id|added_time|message|is_viewed
这里from_member_id和to_member_id是成员表的主键。message是相互发送的消息，is_vied是表示接收方是否查看消息或to_member_id的标志。所以，我有一个member_id，现在我必须将其与两列匹配，但我希望结果应该按照未读消息排序，然后是未读消息。
我正在使用此查询
SELECT `from_member_id`
, `to_member_id`
, MAX(`added_time`) AS latest
, `is_viewed`
FROM `wp_doc_profile_message`
WHERE `from_member_id` = 3
GROUP BY `from_member_id`
ORDER BY `is_viewed` ASC, `latest` DESC
它并没有给我渴望的结果。请帮忙。
在这里，您可以将to_member_id与OR运算符和group by id消息表id匹配，这样记录就不会重复
SELECT `from_member_id`,`to_member_id`,max(added_time) as latest,`is_viewed` FROM
wp_doc_profile_message WHERE from_member_id = 3 OR to_member_id=3
group by `id`
ORDER BY is_viewed ASC, latest DESC
或
SELECT pm.`from_member_id`,pm.`to_member_id`,max(pm.added_time) as latest,pm.`is_viewed` FROM
wp_doc_profile_message pm
LEFT JOIN members m ON (m.id=pm.`from_member_id` OR m.id=pm.`to_member_id` )
group by pm.`id`
ORDER BY pm.is_viewed ASC, latest DESC