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

Question

我正在编写一个论坛系统，我正在尝试获取主题中的最后一篇帖子。问题是我正在根据主题 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

Answer 1

你可以做类似的事情

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 )

只需正确设置您的选择，并可能为子查询之外的帖子 JOIN 添加别名