MySQL group by 和 max 返回错误的行

Question

我有两个表，我试图找到每天得分最高的“帖子”。

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;

Answer 1

我经常遇到这个问题。当 MySQL 运行聚合函数时，对于任何非聚合列，它只是提取它为该组运行的第一个数据，无论它是否来自 MAX 行。因此，您要做的是在内部查询中对数据进行排序，以使最大值在其组中排在第一位。看看这是否适合您:

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 个。