mysql - 尝试根据日期查找表的最大行时得到奇怪的值

Question

我正在尝试做一些我认为相对简单的事情。获取用户最后输入的值。我有一个表，用于跟踪他们所有名为 plan_activities_logs 的条目。我使用此语句获取有关单个用户的所有事件:

SELECT created_at as last_active, plan_id, plan_value 
from plan_activity_logs where plan_id IN (select id from plans where tile_id = 30);

它给了我一个看起来像这样的表:

但是当我尝试做这样的事情时:

SELECT MAX(created_at) as last_active, plan_id, plan_value from plan_activity_logs where plan_id IN (select id from plans where tile_id = 30);

我明白了:

虽然日期和 ID 正确，但计划值是错误的值。知道我做错了什么吗？

Answer 1

Any idea what I'm doing wrong?

你没有做错任何事。根据the manual

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

换句话说，这个行为是符合规范的。查询返回一个随机的非确定性值，因为您正在使用 MySql 非标准扩展来分组查询。