PHP MySQL按Group By获取最新记录，而不是第一条记录。

Question

表格：

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

但是这会返回帖子ID 81和83

- shanebp

Answer 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;

- Terje D.

---

我使用了您的第一条查询，但在修改之前它无法工作。我将其从“post_time = ...”更改为“post_time IN ...”，因为我得到了多个结果。 - Benjamin Karlog

为什么这会是个麻烦事呢？使用SQL，只需SELECT * FROM [table] GROUP BY [column]即可选择最新的而不是最旧的，这似乎很合理，对吧？ - Jean-Paul

还要添加如何添加计数。 - shreyas d

Answer 2

它不是很漂亮，但它能够正常运行：

SELECT * FROM (SELECT  post_id, forum_id, topic_id FROM posts
ORDER BY post_time DESC) as temp
GROUP BY topic_id

- Alvar FinSoft Soome

---

这将删除重复项，但结果按主题ID升序排列。 - shanebp

将这段代码添加到您的查询中使其正常工作：ORDER BY post_time DESC LIMIT 5。 - shanebp

Answer 3

尝试类似以下的内容：

SELECT post_id, forum_id, topic_id 
FROM   (SELECT post_id, forum_id, topic_id
        FROM posts
        ORDER BY post_time DESC) 
GROUP BY topic_id 
ORDER BY topic_id desc
LIMIT 0,5

改变 order by 和 limit 条件以满足需要。

- NappingRabbit

---

结果是一个空数组。 - shanebp

我稍微编辑了一下查询语句。我不明白为什么它不能工作。虽然我没有尝试过，但这是一种常见的技术——嵌套查询。假设内部查询返回正确的结果，外部查询应该从中得出结果。 - NappingRabbit

如果您想进行调试，请先运行内部查询。 - NappingRabbit

就性能而言，这似乎比已接受的选项更好。我们有一个大表，在条件中使用子查询需要约45秒，而这种方法运行不到一秒钟。 - Selvam

必须运行相同的查询，我认为将子查询放置在“尽可能高的位置”，并将条件过滤保存为编程解决方案是最佳实践...这几乎总是更好的性能。每个人都喜欢做花哨的连接，但优雅的解决方案必须让位于实用性。免责声明：此评论在任何方面都没有帮助，但我不认为它会削弱原始线程。 - NappingRabbit

Answer 4

也许这不是最好的做法，但有时候函数group_concat()非常有用。它会返回一个按您想要的方式排序并用逗号分隔的所有聚合值的字符串（匹配值用空格分隔）。之后我使用函数SPLIT_STRING()来拆分字符串中的第一个ID。

SELECT  
post_id, 
SPLIT_STRING( group_concat( forum_id, post_time ORDER BY post_time DESC ) ,' ',1 )as forum_id, 
SPLIT_STRING( group_concat( topic_id, post_time ORDER BY post_time DESC ) ,' ',1 )as topic_id ,
FROM posts 
GROUP BY topic_id 
ORDER BY post_time DESC
LIMIT 5

因此，聚合的forum_id和post_time将如下所示：

81 2012-11-19 06:59:04,83 2012-11-19 16:07:46

因此，您需要使用整数和日期时间对的字符串表示形式，每个对之间用逗号分隔，因此我使用了此函数来获取第一个INT：

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');

注意：函数SPLIT_STRING(str, delim, pos)在此处找到：MySQL中用于处理字符串的explode()等效函数。

- Grégoire Lafortune

Answer 5

这对你也能很好地工作。

SELECT *
FROM (
  SELECT post_id, forum_id, topic_id FROM posts
  ORDER BY post_time DESC
  LIMIT 5
) customeTable
GROUP BY topic_id

- Vikash