MYSQL 返回带有 GROUP BY 和 ORDER BY 的奇怪结果 - MYSQL returning strange results with GROUP BY and ORDER BY

MYSQL returning strange results with GROUP BY and ORDER BY

本文关键字：BY 结果 ORDER GROUP MYSQL 返回 | 更新日期: 2023-09-27

尝试进行论坛系统优化，在一个查询中选择所有最后的海报并将它们存储在一个数组中。问题是数据库返回意外结果

PHP 版本 5.3.13

MySQL 版本 5.1.63

$getPosts = $dB->fetch('
  SELECT
    post_id, post_poster_id, post_topic_id, post_time,
    COUNT(post_id) as count
  FROM forum_posts
  WHERE post_topic_id IN (
    SELECT topic_id
    FROM forum_topics
    WHERE topic_forum_id = ' . $forum_id . '
  )
  GROUP BY post_topic_id
  ORDER BY post_time DESC
');
foreach($getPosts as $lastPoster)
{
    $lastPosts[$lastPoster['post_topic_id']] = $lastPoster;
}

SELECT
   p.post_id, p.post_poster_id, p.post_topic_id, p.post_time, l.count
FROM forum_posts AS p
-- joining with list of latests in a derived table
INNER JOIN
-- getting latest time and count
(SELECT p.post_topic_id, MAX(post_time) AS post_time, COUNT(post_id) AS count
FROM forum_posts AS p
-- filtering forum_id with join, join is usually preferrable than subquery
INNER JOIN forum_topics AS t ON t.topic_id = p.post_topic_id AND t.topic_forum_id = '$forum_id'
GROUP BY post_topic_id)
AS l USING(post_topic_id, post_time)
-- grouping to strip possible post_time-in-one-topic-duplicates
GROUP BY p.post_topic_id