php mysql Group By获取最新记录，而不是第一条记录

一尘不染

php mysql Group By获取最新记录，而不是第一条记录

mysql

桌子：

(`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？

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

但是，Group By似乎获得了“第一”记录，而不是“最新”记录。

我尝试将查询更改为

SELECT  post_id, forum_id, topic_id, MAX(post_time)

但这同时返回post_id 81和83

阅读 481

2020-05-17

共1个答案

一尘不染

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

然后应将查询写为

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;

2020-05-17