MySQL返回多行错误怎么处理_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;