桌子：
(`post_id`,`forum_id`,`topic_id`,`post_time`) (79,8,4,'2012-11-19 06:58:08'); (80,3,'2012-11-19 06:58:42'),(81,9,'2012-11-19 06:59:04'),(82,11,6,'2012-11-19 16:05:39'),(83,'2012-11-19 16:07:46'),(84,'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？
他们都有相同的论坛和主题标识,但post_id – > 81的日期大于post_id – > 83.
但是看来,Group By获得了“第一”的记录,而不是“最新的”记录.
我尝试将查询更改为
SELECT post_id,topic_id,MAX(post_time)
但是这会返回post_id 81和83
如果您选择在group子句中未使用的属性,而不是聚合,那么该结果是未指定的.我不知道从哪些行选择其他属性. (sql标准不允许这样的查询,但MysqL更放松).
原文链接：https://www.f2er.com/php/140191.html然后应该将查询写入如
SELECT post_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,post_id ORDER BY post_time DESC LIMIT 5;
要么
SELECT post_id,topic_id FROM posts NATURAL JOIN (SELECT forum_id,max(post_time) AS post_time FROM posts GROUP BY forum_id,topic_id) p ORDER BY post_time LIMIT 5;
