mysql - Max 函数与其他列中的值存在问题

Question

我有以下 MySQL 查询:

Select match_static_id, comments as last_comment, max(timestamp)
from comments as c 
group by match_static_id;

我有比赛评论表，我想获得每场比赛的最新评论。所以我使用 max(timestamp) 和 group by (match_static_id) 来实现这一点，但我的问题是我得到了按 match_static_id 分组的最大时间戳，但我得到了其他评论(不是最大时间戳的评论)我的查询顺序是否错误？

Answer 1

我不是mysql专家，但我能感觉到这个问题。可能是因为注释不属于 group by，它会返回与 match_static_id 匹配的所有行。我建议重写如下内容:

select match_static_id, (select Comment from comments c where c.timestamp =max(a.timestamp)) as last_comment, max(timestamp) from comments group by match_staic_id

或

select c.match_static_id, c.comments as last_comment, c.timestamp from comments c inner join (Select max(timestamp) as ts from comments group by match_static_id) temp c.timestamp = temp.ts