mysql - 为什么要通过不工作来订购？

Question

我有以下查询:

select DISTINCT CASE WHEN msg_from = 35 
    then msg_to else msg_from 
end msg_from_result , msg_id,msg_from,msg_to,msg_content,msg_status,msg_created_at,
    (select user_name from users where user_id = msg_from_result) user_name
    from messages 
    where 35 in (msg_from , msg_to) 
group by msg_from_result 
    order by msg_id desc

这应该检索包含最后一条属于它的消息的对话。对话已检索，但这里的问题是我无法使用 ORDER BY 通过设置 ORDER BY msg_id DESC 来获取最后一条消息。

这有什么问题吗？还有其他方法吗？

Answer 1

我想我明白你想要做什么，但这是我的写法(如果我理解正确的话):

select users.user_name, msg_id, msg_from, msg_to, msg_content, msg_status, msg_created_at
from (
select if(msg_from = 35,msg_to,msg_from) as msg_from_result
, max(msg_id) as last_message
from messages 
where 35 in (msg_from,msg_to) 
group by msg_from_result,last_message
) der_table -- creating a table of conversations and the last message ids
join users on user_id=der_table.msg_from_result
join messages on messages.msg_id=der_table.last_message
order by msg_id desc

您需要先获取最后一条消息 ID，然后才能获取消息内容等。