MySQL查询在GROUP BY中排序结果 [英] MySQL query order the results in GROUP BY

问题描述

我正在编写一个论坛系统，我正在尝试在主题中获得最后一篇文章。问题是我将主题ID上的结果分组，并且我无法找到一种方法来获取最后回复，以便在分组数据中显示。

这是我的查询到目前为止：

pre $ 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`最后一个ON（last.user_id = posts.user_id）
WHERE fcat_id =' 2'
GROUP BY topics.topic_id


解决方案

你可以做一些事情g

  SELECT *，`last`.`user_id` AS last_user_id FROM 
（
 SELECT用户.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 last` ON（`last`.user_id = posts.post_id）
  

只需正确设置您的选择，并且可以使用子查询之外的帖子JOIN

I'm coding a forum system and I'm trying to get the last post in a topic. The problem is I'm grouping the results on the topic id and I can't figure out a way to get the last reply to be displayed in the grouped data.

Here is my query so far:

   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

解决方案

You can do something like

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 )

Just set your selects properly and maybe alias the posts JOIN which is outside the subquery

这篇关于MySQL查询在GROUP BY中排序结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！