mysql - 返回子查询中的最后一条记录 - MySQL

Question

所以我正在为我的网站创建一个类似于 Facebook 风格的消息系统（我知道是对的）。我打算发生的是列出用户的所有对话，并返回对话中的最后一条消息。问题是我在进行 GROUP BY 时收到第一条消息。也许我正在以错误的方式处理它。

数据库的结构由三个表组成。对话，对话分配，对话消息

对话只存储对话的外部数据，重要的是id

conversations_assign就是这样做的。它列出了对话中每个人的用户 ID

conversations_msg显然存储了消息，即与其他数据一起发布的用户。

一切都与conversations.id = conv_id联系在一起

这是我的查询，正如我在上面用 GROUP BY 所说的那样，它返回第一个结果。取消 GROUP BY 并添加 LIMIT 1 从字面上返回一条消息，其余为空。任何帮助表示赞赏，谢谢！

SELECT conversations.id,
    conversations_assign.num_unread,
    message
FROM conversations_assign
INNER JOIN conversations ON conversations_assign.conv_id=conversations.id                                     
LEFT JOIN (
    SELECT conv_id, message, msg_time
    FROM conversations_msg
    GROUP BY conv_id
) AS message ON message.conv_id=conversations_assign.conv_id
WHERE conversations_assign.userid='XXsomeidXX'
ORDER BY message.msg_time DESC

标签： mysqlsqldatetimemaxgreatest-n-per-group

Answer 1

你不能用聚合做你想做的事。相反，您需要过滤. 如果您运行的是 MySQL 8.0，我会推荐窗口函数：

select c.id, ca.num_unread, cm.message
from conversations_assign c
inner join conversations ca on ca.conv_id =c.id                                     
left join (
    select cm.*, rank() over(partition by conv_id order by msg_time desc) rn
    from conversations_msg cm
) as cm on cm.conv_id = ca.conv_id and cm.rn = 1
where ca.userid = 'xxsomeidxx'
order by cm.msg_time desc

在早期版本中，另一种方法使用相关子查询来过滤left join：

select c.id, ca.num_unread, cm.message
from conversations_assign c
inner join conversations ca 
    on  ca.conv_id =c.id                                     
left join conversations_msg cm
    on  cm.conv_id = ca.conv_id 
    and cm.msg_time = (
        select max(cm1.msg_time) from conversations_msg cm1 where cm1.conv_id = ca.conv_id
    )
where ca.userid = 'xxsomeidxx'
order by cm.msg_time desc