MySQL检索组的最新记录 [英] MySQL retrieve latest record for Group

问题描述

我有一个社交网站，并且正在努力处理查询。我有一个包含所有用户帖子的帖子表，然后是包含帖子上所有评论的post_comments表。我正在尝试从post_comments表中发布最新评论。 post_comments表格包含以下列：

post_comment_id，
post_id，
writer_user_id，
post_comment_content，
datetime p>

我通过post_id将结果分组如下：

  SELECT * FROM post_comments GROUP BY post_id 
  

这几乎是我想要的，但它总是返回每个帖子的最旧评论最新的一个。我怎样才能得到它返回每个职位的最新评论？

解决方案

GROUP BY旨在用于聚合函数，否则任意选择每组一行。你的解决方案（上面和你自己的答案）的作品，因为MySQL似乎保持每个组的第一行，但你不能保证这会始终发生。

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

 从post_comments group中选择post_id，MAX（datetime）作为post_id的最新值
  

使用它选择最新的评论：

  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 
  

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_comment_id, post_id, writer_user_id, post_comment_content, datetime

I have grouped the results by post_id like so:

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?

解决方案

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.

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

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

这篇关于MySQL检索组的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！