SQL Server：如何拆分跨天运行时长为多行记录？

这个需求在设备运行数据处理里挺常见的——当设备运行时长跨天的时候，需要把当天剩余时长和次日的部分拆分成两条独立记录。下面是针对你的场景的完整实现方案：

先准备测试数据 #

首先我们模拟你的原始数据表结构和数据：

DECLARE @DeviceRun TABLE (C1 VARCHAR(5), Start_Time DATETIME, Minutes INT);
INSERT INTO @DeviceRun VALUES ('I1', '2017-08-06 23:50:00', 40);

核心拆分SQL语句 #

我们通过计算当天24点（次日0点）与开始时间的差值，结合UNION ALL来生成拆分后的记录：

-- 第一条记录：当天剩余的运行时长
SELECT
    C1,
    Start_Time AS [Time],
    -- 计算从Start_Time到次日0点的分钟数
    DATEDIFF(MINUTE, Start_Time, DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0)) AS Minutes
FROM @DeviceRun
-- 只筛选跨天的记录
WHERE DATEADD(MINUTE, Minutes, Start_Time) > DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0)

UNION ALL

-- 第二条记录：次日的运行时长
SELECT
    C1,
    -- 次日0点作为这条记录的开始时间
    DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0) AS [Time],
    -- 总时长减去当天剩余时长，得到次日的时长
    Minutes - DATEDIFF(MINUTE, Start_Time, DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0)) AS Minutes
FROM @DeviceRun
WHERE DATEADD(MINUTE, Minutes, Start_Time) > DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0)

-- 补充：如果有不跨天的记录，直接返回原记录
UNION ALL

SELECT C1, Start_Time AS [Time], Minutes
FROM @DeviceRun
WHERE DATEADD(MINUTE, Minutes, Start_Time) <= DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0);

逻辑说明 #

• 次日0点的计算：DATEADD(DAY, DATEDIFF(DAY, 0, Start_Time) + 1, 0)是SQL Server里获取某一天次日0点的常用写法，它会把Start_Time截断到日期部分，再加1天得到次日0点。
• 跨天判断：通过DATEADD(MINUTE, Minutes, Start_Time) > 次日0点来判断这条记录是否需要拆分。
• 时长拆分：当天的时长是开始时间到次日0点的分钟差，次日的时长就是总时长减去当天的时长。

执行完这段SQL后，你会得到期望的结果：

C1 | Time                | Minutes
----|---------------------|---------
I1  | 2017-08-06 23:50:00 | 10
I1  | 2017-08-07 00:00:00 | 30

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