MySQL检索Group的最新记录

I have a social networking site and am struggling with a query. I have a posts table that holds all of the users posts and then a post_comments table that holds all comments on a post. I am trying to find the latest comment by post from post_comments table. The post_comments table has the following columns:

我有一个社交网站,我正在努力查询。我有一个帖子表,其中包含所有用户帖子,然后是一个post_comments表,其中包含帖子上的所有评论。我试图通过post_comments表中的帖子找到最新评论。 post_comments表包含以下列:

post_comment_id, post_id, writer_user_id, post_comment_content, datetime

post_comment_id,post_id,writer_user_id,post_comment_content,datetime

I have grouped the results by post_id like so:

我按照post_id对结果进行了分组,如下所示:

SELECT * FROM post_comments GROUP BY post_id

This almost does what I want but it always returns the oldest comment for each post not the newest one. How can I get it to return the newest comment for each post?

这几乎可以满足我的需求,但它总是会返回每个帖子最旧的评论,而不是最新的评论。如何让它返回每篇帖子的最新评论?

3 个解决方案

#1

GROUP BY is intended to be used with aggregating functions, otherwise it arbitrarily selects one row per group. Your solution (above and in your self-answer) works because MySQL seems to be keeping the first row of each group, but you're not guaranteed that this will always happen.

GROUP BY旨在与聚合函数一起使用,否则它会随意选择每组一行。你的解决方案(上面和你的自我回答)是有效的,因为MySQL似乎保留了每个组的第一行,但你不能保证这总是会发生。

You can get the date of the latest comment for each post_id like this.

您可以像这样获取每个post_id的最新评论的日期。

select post_id, MAX(datetime) as latest from post_comments group by post_id

Use it to select the latest comment:

用它来选择最新评论:

SELECT t1.* FROM post_comments AS t1
JOIN (
    SELECT post_id, MAX(datetime) AS latest FROM post_comments GROUP BY post_id
) AS t2
ON t1.post_id = t2.post_id AND t1.datetime = t2.latest

#2

As so many questions come in with "I want the latest entry for...", and have some date time. If the table in question is auto-increment, and the date/time stamp will ALWAYS stay in sequential time correlation ... i.e.: the user has no chance to edit or otherwise change the timestamp on the record to put today's entry with a date of 3 weeks ago... so many will keep trying to get that last date and re-join on a non-advantaged key. Just get the maximum ID key for the post and use that... I would have an index on the post ID and the primary auto-increment key.

因为有很多问题出现在“我想要最新的条目......”,并且有一些约会时间。如果有问题的表是自动递增的,并且日期/时间戳将始终保持顺序时间关联...即:用户没有机会编辑或以其他方式更改记录上的时间戳以将今天的条目与日期相关联3个星期前...很多人会继续尝试获得最后的约会并重新加入一个无利可图的密钥。只需获取帖子的最大ID密钥并使用...我会在帖子ID和主要自动增量键上有一个索引。

select
      P2.*
   from
      ( select post_id, max( post_comment_id ) as LastPost
           from post_comments
           group by post_id ) LastPerPost
         JOIN post_comments P2
            on LastPerPost.LastPost = P2.post_comment_id

#3

-3

Sorted it! I did it like this:

排序吧!我是这样做的:

SELECT * FROM (
    SELECT * FROM post_comments ORDER BY datetime DESC
) AS post_comments 
GROUP BY post_id

#1