用于在一张表中显示和排序消息及其回复的 SQL

Question

我为正在开发的 CMS 编写了一个简单的消息系统，但很难找到获取所有消息及其按最新顺序排序的回复（消息或回复）的最佳方法。

我有一个名为“messages”的表，其中包含以下字段： *id, active[1,0], subject, message, datetime, user_from, user_to,reply_id*

这一切都很简单，*reply_id* 对于主级消息来说是空的，并且包含父 id回复。我不知道如何编写 SQL 来根据他们自己和他们的回复以 datetime DESC 顺序获取所有主要级别的消息。我确信这是通过某种 UNION 完成的，但我缺乏 SQL 技能。如果相同的 SELECT 可以给出每个主级别消息中的回复计数，那就太棒了！

任何帮助将不胜感激！

Answer 1

已经很晚了，我很累，但我想你可以尝试这样的事情：

SELECT main.*, COUNT(reply.id) AS cnt_replies, MAX(reply.datetime) AS max_datetime
FROM posts AS main
LEFT JOIN posts AS reply
    ON main.id = reply.reply_id
GROUP BY main.id
HAVING main.reply_id IS NULL
ORDER BY max_datetime DESC

编辑：固定查询
EDIT2：现在包含没有任何回复的消息