php - MySQL查询在GROUP BY中对结果进行排序

我正在编写一个论坛系统，我正在试图得到一个主题的最后一篇文章。问题是我正在按主题id对结果进行分组，我无法找到在分组数据中显示最后一个答复的方法。
到目前为止，我的问题是：

   SELECT SQL_CACHE users.user_id,
          users.username,
          topics.title,
          topics.topic_id,
          topics.previews,
          topics.date_added,
          posts.post_id,
          last.username AS last_username,
          last.user_id AS last_user_id,
          MAX( posts.post_id ) AS last_post_id,
          posts.date_added AS last_data
     FROM `topics`
LEFT JOIN `users` ON users.user_id = topics.user_id
LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )
LEFT JOIN `users` AS last ON ( last.user_id = posts.user_id )
    WHERE fcat_id = '2'
 GROUP BY topics.topic_id

最佳答案

你可以这样做

SELECT *, `last`.`user_id` AS last_user_id FROM
(
    SELECT users.user_id,
          users.username,
          topics.title,
          topics.topic_id,
          topics.previews,
          topics.date_added,
          posts.post_id,
          MAX( posts.post_id ) AS last_post_id,
          posts.date_added AS last_data
         FROM `topics`
    LEFT JOIN `users` ON users.user_id = topics.user_id
    LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )
        WHERE fcat_id = '2'
     GROUP BY topics.topic_id
 ) AS `tst`
LEFT JOIN `posts` ON ( posts.post_id = tst.last_post_id )
LEFT JOIN `users` AS `last` ON ( `last`.user_id = posts.post_id )

只需正确设置您的选择，并可能将子查询外部的posts连接命名为

关于php - MySQL查询在GROUP BY中对结果进行排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5929510/