gpt4 book ai didi

php mysql Group By获取最新记录,而不是第一个记录

转载 作者:IT老高 更新时间:2023-10-28 23:59:13 24 4
gpt4 key购买 nike

table :

(`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,但 post_id -> 81 的日期比 post_id -> 83 的日期更早。

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

我尝试将查询更改为

SELECT  post_id, forum_id, topic_id, MAX(post_time)

但这会同时返回 post_id 81 和 83

最佳答案

如果您选择的属性未在组子句中使用且不是聚合,则结果未指定。 您不知道其他属性是从哪些行中选择的。 (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;

关于php mysql Group By获取最新记录,而不是第一个记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13459516/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com