SQL Server：按ID与日期分组获取每日最大时间对应数据

解决方案 #

方法1：窗口函数ROW_NUMBER()（推荐）

这是SQL Server中最直观且高效的实现方式，通过ROW_NUMBER()按ID和日期（天）分区，对每个分区内的记录按时间倒序排序，筛选出排序序号为1的记录：

WITH RankedData AS (
    SELECT 
        ID,
        Value,
        Date,
        ROW_NUMBER() OVER (
            PARTITION BY ID, CAST(Date AS DATE) 
            ORDER BY Date DESC
        ) AS RowNum
    FROM YourTableName -- 替换成你的表名
)
SELECT ID, Value, Date
FROM RankedData
WHERE RowNum = 1;

方法2：MAX()关联查询

先分组计算每个ID每天的最新时间，再通过关联原表获取对应记录：

SELECT t.ID, t.Value, t.Date
FROM YourTableName t
JOIN (
    SELECT 
        ID,
        CAST(Date AS DATE) AS DayDate,
        MAX(Date) AS LatestTime
    FROM YourTableName
    GROUP BY ID, CAST(Date AS DATE)
) latest ON t.ID = latest.ID AND t.Date = latest.LatestTime;

方法3：TOP 1 WITH TIES 简化写法

利用TOP 1 WITH TIES特性，结合窗口函数直接返回所有符合条件的记录：

SELECT TOP 1 WITH TIES
    ID, Value, Date
FROM YourTableName
ORDER BY ROW_NUMBER() OVER (
    PARTITION BY ID, CAST(Date AS DATE) 
    ORDER BY Date DESC
);

内容的提问来源于stack exchange，提问作者Gerardo Gomez Arizmendi