如何按日期从DateTime字段中筛选出每日的最新记录

错误原因 #

你编写的SQL存在两个核心问题：

• 分组逻辑错误：GROUP BY start是按精确到秒的完整时间值分组，没有按自然日聚合，无法得到每天的最大时间
• 取值逻辑错误：子查询中同时选择id和MAX(start)，在非严格SQL模式下，数据库会随机返回同组内的id，无法保证返回的id就是对应最大时间的那条记录，所以出现其他字段和时间不匹配的问题

正确实现方案 #

方案1：通用关联写法（兼容所有数据库版本） #

先按自然日分组拿到每天的最大时间，再关联原表匹配对应行的所有字段：

SELECT mt.id, mt.start, mt.Value, mt.Value2, mt.Value3
FROM myTable mt
INNER JOIN (
    -- 按自然日分组取每天最大的start时间
    SELECT DATE(start) AS natural_day, MAX(start) AS max_daily_start
    FROM myTable
    GROUP BY DATE(start)
) daily_max
ON DATE(mt.start) = daily_max.natural_day
AND mt.start = daily_max.max_daily_start

注：不同数据库提取日期的函数略有差异：MySQL用DATE(start)，SQL Server用CAST(start AS DATE)，Oracle用TRUNC(start)，按需替换即可。

方案2：窗口函数写法（适配MySQL 8.0+、PostgreSQL、SQL Server等新版数据库） #

用窗口函数按自然日分区、按时间倒序排序，直接取每个分区的第一条记录即可：

SELECT id, start, Value, Value2, Value3
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY DATE(start) ORDER BY start DESC) AS row_num
    FROM myTable
) temp
WHERE row_num = 1

如果存在同一天两条记录start完全相同的场景，需要都返回时，把ROW_NUMBER()替换为RANK()即可。

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