MySQL group by和max返回错误的行

我有两张桌子,我试着找到每天得分最高的“帖子”.

CREATE TABLE IF NOT EXISTS `posts_points` (
  `post_id` int(10) unsigned NOT NULL,
  `comments` smallint(5) unsigned NOT NULL,
  `likes` smallint(5) unsigned NOT NULL,
  `favorites` smallint(5) unsigned NOT NULL,
   PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `posts` (
  `profile_id` int(10) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,
  `pubdate_utc` datetime NOT NULL,
  PRIMARY KEY (`post_id`),
  KEY `profile_id` (`profile_id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

我已经尝试过以下查询.它返回正确的分数,但其他列只是随机行.我究竟做错了什么 ？

SELECT p.post_id, p.profile_id
   , MAX(t1.score)
   , DATE_FORMAT(t1.pubdate_utc, '%d %b') post_date
   , DATE(t1.pubdate_utc) mydate
FROM
(
   SELECT p.profile_id, p.post_id, p.pubdate_utc
      , (pp.comments + pp.likes + pp.favorites) AS score
   FROM posts p 
   INNER JOIN posts_points pp ON p.post_id = pp.post_id
) t1
INNER JOIN posts p ON t1.post_id = p.post_id
   AND t1.pubdate_utc = p.pubdate_utc
GROUP BY mydate
ORDER BY mydate DESC
LIMIT 18;

我一直遇到这个问题.当 MySQL 运行聚合函数时,对于任何非聚合列,它只是拉出它为该组运行的第一个数据,无论它是否来自MAX行.因此,您需要做的是在内部查询中对数据进行排序,使得maxes在其组中是第一位的.看看这是否适合你：

SELECT t.post_id,
       t.profile_id,
       t.score,
       t.pubdate_utc
FROM (SELECT p.profile_id,
             p.post_id,
             p.pubdate_utc,
             (pp.comments + pp.likes + pp.favorites) score
      FROM posts p
      JOIN posts_points pp ON p.post_id = pp.post_id
      WHERE p.pubdate_utc >= DATE_ADD(DATE(NOW()), INTERVAL -17 DAY)
      ORDER BY score DESC
     ) t
GROUP BY DATE(t.pubdate_utc) DESC
;

请注意,我在这里没有使用MAX功能.按分数降序排序,然后在外部查询中按日期分组将按日期提取最高分.另请注意,我将WHERE子句放在内部查询中.像这样的内部查询(有时是必要的)不是很有效,因为它们没有外部查询的优化索引,所以要确保你的内部结果集尽可能小.最后,请注意GROUP BY DATE(t.pubdate_utc).如果我没有将其简化为日期信息,则会有超过18个结果,因为时间也会计算在内.

编辑：更改为INTERVAL -17 DAY,最多可提供18个结果,而不是19个.