提取最近发布的 MySQL 主题摘要

Question

我有一个非常基本的论坛系统。三个主表（主题、评论和用户）拥有您能想象到的所有常用字段，例如相互之间的自动增量主键和外键，所以我认为我不需要详细说明这

一切我想做的是获取最近发布的主题列表。列表视图应显示主题标题、初始评论/帖子的正文以及作者姓名，但列表应按最近发布的主题排序。

除了最基本的查询之外，我不太擅长 MySQL。显然，我可以将其分解为多个查询，但为了提高性能，我希望将其放在一个查询中。

这是我首先想到的，但它根据第一篇文章的时间对主题进行排序。

SELECT topic.id as topic_id, topic.title as topic_title, topic.datePosted as datePosted, topic.views as views, topic.numComments as numComments,
    comment.ID as commentID, comment.body as body,
user.ID as userID, user.firstName, user.lastName, user.title as user_title, user.city, user.state, user.thumbnail

FROM comment INNER JOIN topic ON comment.topicID = topic.id
INNER JOIN user ON comment.userID = user.id
WHERE comment.id
        IN (
        SELECT min( comment.id )
        FROM COMMENT INNER JOIN topic ON comment.topicID = topic.id
        GROUP BY topic.id
        )

ORDER BY commentID desc';

Answer 1

好的，这是我对您所要求的内容的第一次攻击，即每个主题中最近发布的评论的列表。如果这不是您正在寻找的内容，请告诉我：

SELECT
  t.title,
  t.views,
  t.numComments,
  c.id,
  c.datePosted,
  c.body,
  u.firstName,
  u.lastName
FROM
  topic AS t
    INNER JOIN
      ( SELECT
          c_inner.topicId,
          MAX(c_inner.datePosted) AS latestPosted
        FROM
          comment AS c_inner
        GROUP BY
          c_inner.topicId ) AS cmax
      ON t.id = cmax.topicId
    INNER JOIN comment AS c
      ON c.topicId = cmax.topicId AND c.datePosted = cmax.latestPosted
    INNER JOIN user AS u
      ON c.userId = u.id
ORDER BY c.datePosted DESC;

如果我误解了您正在寻找的内容，您可以发布您想要的结果的示例吗？