SELECT子句中使用DATEDIFF仅返回首行的原因咨询

原表结构与数据 #

Activity表的结构和数据如下：

执行的查询语句 #

select player_id, datediff(event_date, min(event_date)) as date_diff
from Activity

实际返回结果 #

预期返回结果 #

异常原因 #

你的查询触发了数据库的隐式聚合行为，核心问题是聚合函数与非聚合字段混用但未指定GROUP BY子句：

• min(event_date)是全局聚合函数，计算出整个表中最早的日期为2016-02-02；
• 当SELECT子句中同时存在非聚合字段（player_id）和聚合函数时，MySQL等数据库会默认对全表进行隐式分组，此时非聚合字段会随机选取分组内的某一行值（这里恰好取了表中第一行的player_id=1）；
• 最终计算的是该行event_date（2016-03-01）与全局最小日期的差值，得到结果28，因此只返回一行非预期结果。

正确实现预期需求的写法 #

如果要返回拥有最小event_date的行且date_diff为0，可以用以下两种方式：

方式一：子查询筛选最小日期 #

select player_id, datediff(event_date, event_date) as date_diff
from Activity
where event_date = (select min(event_date) from Activity);

方式二：排序后取首行 #

select player_id, 0 as date_diff
from Activity
order by event_date asc
limit 1;

内容的提问来源于stack exchange，提问作者Thompson