php mysql Group By获取最新记录，而不是第一条记录 [英] php mysql Group By to get latest record, not first record

问题描述

表格:

(`post_id`, `forum_id`, `topic_id`, `post_time`) 
(79, 8, 4, '2012-11-19 06:58:08');
(80, 3, 3, '2012-11-19 06:58:42'),
(81, 9, 9, '2012-11-19 06:59:04'),
(82, 11, 6, '2012-11-19 16:05:39'),
(83, 9, 9, '2012-11-19 16:07:46'),
(84, 9, 11, '2012-11-19 16:09:33'),

查询:

SELECT  post_id, forum_id, topic_id FROM posts 
GROUP BY topic_id 
ORDER BY post_time DESC
LIMIT 5

结果:

[0] => [post_id] => 84 [forum_id] => 9 [topic_id] => 11  
[1] => [post_id] => 82 [forum_id] => 11 [topic_id] => 6  
[2] => [post_id] => 81 [forum_id] => 9 [topic_id] => 9  
[3] => [post_id] => 80 [forum_id] => 3 [topic_id] => 3  
[4] => [post_id] => 79 [forum_id] => 8 [topic_id] => 4

问题:

如何重写查询，使其返回post_id-> 83而不是post_id-> 81?

How to rewrite the query so that it returns post_id -> 83 instead of post_id -> 81 ?

它们的论坛和主题ID都相同，但是post_id-> 81的日期早于post_id->83.

They both have the same forum and topic ids, but post_id -> 81 has an older date than post_id -> 83.

但是，Group By似乎获得了第一"记录而不是最新"记录.

But it seems that Group By gets the 'first' record and not the 'newest' one.

我尝试将查询更改为

SELECT  post_id, forum_id, topic_id, MAX(post_time)

但是返回post_id 81和83

but that returns both post_id 81 and 83

推荐答案

如果选择的是group子句中未使用且未聚合的属性，则结果不确定. 即，您不知道其他属性是从哪些行中选择的. (sql标准不允许此类查询，但MySQL更宽松).

If you select attributes that are not used in the group clause, and are not aggregates, the result is unspecified. I.e you don't know which rows the other attributes are selected from. (The sql standard does not allow such queries, but MySQL is more relaxed).

然后应将查询写为

SELECT post_id, forum_id, topic_id
FROM posts p
WHERE post_time =
  (SELECT max(post_time) FROM posts p2
   WHERE p2.topic_id = p.topic_id
   AND p2.forum_id = p.forum_id)
GROUP BY forum_id, topic_id, post_id
ORDER BY post_time DESC
LIMIT 5;

或

SELECT post_id, forum_id, topic_id FROM posts
NATURAL JOIN
(SELECT forum_id, topic_id, max(post_time) AS post_time
 FROM posts
 GROUP BY forum_id, topic_id) p
ORDER BY post_time
LIMIT 5;

这篇关于php mysql Group By获取最新记录，而不是第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！