MySQL 无法正确排序查询

Question

这是我正在开发的自定义论坛。我需要选择所有线程，获取每个线程中的帖子数以及每个线程中的最后一个帖子。我可以获得帖子数量，但我的查询返回第一篇帖子而不是最后一篇。

SELECT thread_id, thread_title, p.*, COUNT(p.post_id) AS Posts
   FROM forums_threads
   JOIN forums_posts AS p ON post_thread_id=thread_id
   WHERE thread_forum_id=84
   GROUP BY thread_id
   ORDER BY thread_date DESC, post_date DESC

Answer 1

正如 @mixman 所建议的，您需要链接到 forum_posts 两次:一次获取总计金额(帖子计数和最大帖子日期)，一次获取最后一篇帖子的实际内容(我假设通过“最后”你的意思是“最近”)在线程中:

SELECT ft.thread_id, ft.thread_title, fp.*, pmin.postcount
FROM forums_threads AS ft
JOIN (
      SELECT post_thread_id, MAX(post_date), COUNT(post_id) AS postcount
      FROM forums_posts
      GROUP BY post_thread_id
     ) AS pmin ON ft.thread_id=pmin.post_thread_id
JOIN forums_posts AS fp ON fp.post_thread_id=pmin.post_thread_id AND fp.post_date = pmin.post_date
WHERE ft.thread_forum_id=84
ORDER BY ft.thread_date DESC