mysql - SQL JOIN 表和 GROUP BY 以获得正确的行

Question

情况如下，i 代表表 forum_topics、forum_replies 和 users。其中 _topics 包含所有主题，_replies 包含所有帖子，而 users 包含所有用户。

我尝试将主题列出为:

(主题)______________________________< em>____(最后回复时间)

(由用户名创建的主题)__________________(最后回复者)

目前，“主题”和“用户名创建的主题”显示得很好，但是最后发帖的时间和发帖的用户是错误的。

SQL:

 SELECT 
forum_topics.id,
forum_topics.category,
forum_topics.subject,
forum_topics.created AS topiccreate,
forum_topics.createdby AS topiccreatedby,
forum_replies.topic,
forum_replies.created AS repliecreated,
forum_replies.createdby AS repliecreatedby,
usertopic.firstname AS topicfirstname,
usertopic.lastname AS topiclastname,
userreplie.firstname AS repliefirstname,
userreplie.lastname AS replielastname,
usertopic.id as topicid, 
userreplie.id

FROM forum_topics

JOIN forum_replies ON forum_replies.topic = forum_topics.id
JOIN users usertopic ON forum_topics.createdby = usertopic.id
JOIN users userreplie ON forum_replies.createdby = userreplie.id

WHERE forum_topics.category =  '1'
GROUP BY forum_replies.topic
ORDER BY forum_replies.created DESC 

如何才能正确显示“最后回复时间”和“最后回复时间”？我尝试删除分组依据，然后它检索所有帖子，但我只想要每个主题的最新帖子数据。

目前，当使用 GROUP BY 时，它只检索所有主题一次(正确)，但最后回复时间和最后回复时间显示不正确，因为它似乎检索每个主题第一篇文章的数据.

希望你能理解我的问题! :/

Answer 1

您还需要满足一个条件才能获得最新回复。以下是在 join 子句中执行此操作的方法:

FROM forum_topics
JOIN forum_replies ON forum_replies.topic = forum_topics.id
JOIN users usertopic ON forum_topics.createdby = usertopic.id
JOIN users userreplie ON forum_replies.createdby = userreplie.id
JOIN (select topic, max(created) as maxcreated
      from forum_replies
      group by topic
     ) frmax on frmax.topic = forum_replies.topic and
                frmax.maxcreated = forum_replies.created;

我认为执行此操作后您将不需要group by。