如何按最后回复时间降序查询关联表指定数据？

原SQL的核心问题 #

• 无表关联条件，生成笛卡尔积：用from message, reply_message这种隐式连接却没加message.id = reply_message.reply_id的关联条件，会让两张表的所有行交叉匹配，得到的是两张表行数相乘的无效结果，完全偏离业务需求。
• distinct属于治标不治本：因为笛卡尔积产生大量重复数据才用distinct，但这只是掩盖连接逻辑错误，并没有解决数据匹配的本质问题。
• 排序逻辑不符合要求：需求是按单条消息的最后回复时间排序，但原SQL直接用reply_message.date排序，会让同一条消息的每条回复都参与排序，即使distinct去重后，排序依据也不是该消息的最新回复时间，结果混乱。另外，没有回复的消息会被直接排除，不符合实际场景。

修正后的SQL示例 #

要实现按最后回复时间降序、同时保留无回复消息的需求，可以用左连接+分组取最大回复时间的写法：

SELECT 
    m.subject, 
    m.id, 
    m.date,
    MAX(r.date) AS last_reply_date
FROM message m
LEFT JOIN reply_message r ON m.id = r.reply_id
GROUP BY m.subject, m.id, m.date
ORDER BY last_reply_date DESC, m.date DESC;

• 用LEFT JOIN保留所有消息，哪怕没有回复记录；
• MAX(r.date)获取每条消息的最新回复时间；
• 排序时优先按最新回复时间降序，无回复的消息会因last_reply_date为NULL排在最后（如果想让无回复消息按自身发布时间排序，可调整ORDER BY为COALESCE(last_reply_date, m.date) DESC）。

内容的提问来源于stack exchange，提问作者lead9upper