如何在SELECT语句中处理跨午夜记录并重新计算时长

问题背景 #

我有一张StatusHistory表，数据按日收集，当日最后一条记录通常会跨至次日（除非恰好截止于午夜）。表中现有数据如下：

需求 #

执行SELECT查询时，将当日最后一条记录的dateto修改为2022-05-04 23:59:59.999，并重新计算该记录的duration为589，其余记录保持不变，期望结果如下：

我已能通过以下语句获取最后一条记录的Id：

SELECT MAX(Id) FROM (SELECT * FROM [StatusHistory]) as a

但不知如何实现输出所有数据且仅修改最后一条记录的效果。

解决方案 #

可以通过CASE条件判断结合子查询或窗口函数实现需求，以下提供两种方案：

方案一：基于表内最后一条记录（适用于单日期数据） #

直接通过子查询获取最大Id，用CASE语句对最后一条记录的字段进行修改：

SELECT 
    Id,
    datefrom AS 起始时间,
    CASE 
        WHEN Id = (SELECT MAX(Id) FROM StatusHistory) THEN '2022-05-04 23:59:59.999'
        ELSE dateto 
    END AS 结束时间,
    CASE 
        WHEN Id = (SELECT MAX(Id) FROM StatusHistory) THEN 589
        ELSE duration 
    END AS 时长
FROM StatusHistory
ORDER BY Id;

方案二：按日期分组定位当日最后一条记录（通用多日期场景） #

如果表内有多日数据，需要针对每天的最后一条记录处理，可使用窗口函数ROW_NUMBER()按日期分组排序，再动态计算时长：

WITH RankedRecords AS (
    SELECT 
        *,
        -- 按起始时间的日期分组，每组内按Id倒序排名，排名1为当日最后一条
        ROW_NUMBER() OVER (PARTITION BY CONVERT(date, datefrom) ORDER BY Id DESC) AS rn
    FROM StatusHistory
)
SELECT 
    Id,
    datefrom AS 起始时间,
    CASE 
        WHEN rn = 1 THEN '2022-05-04 23:59:59.999'
        ELSE dateto 
    END AS 结束时间,
    CASE 
        WHEN rn = 1 THEN DATEDIFF(MILLISECOND, datefrom, '2022-05-04 23:59:59.999')
        ELSE duration 
    END AS 时长
FROM RankedRecords
ORDER BY Id;

该方案通过DATEDIFF(MILLISECOND, ...)自动计算毫秒级时长，避免手动计算出错，同时支持多日期数据的批量处理。

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