SQL查询需求：计算多组On/Off时间差总和以统计总通电时长

首先得解决一个核心问题：你的表中通电开始（OnTime）和结束（OffTime）是分开的两条记录，所以第一步需要把它们正确配对，之后才能计算每段时长并求和。

核心思路 #

1. 给所有通电开始的记录按时间排序编号，给所有结束的记录也按时间排序编号——因为你的数据是按"开-关-开-关"的顺序插入的，编号相同的就是一组对应的开/关记录。
2. 关联这两组编号匹配的记录，计算每一组的通电分钟数。
3. 对所有组的分钟数求和，同时添加时间范围筛选条件。

完整SQL查询 #

DECLARE @StartDate SMALLDATETIME = '2017-01-01 00:00:00';
DECLARE @EndDate SMALLDATETIME = '2017-01-07 23:59:59';

SELECT 
    SUM(DATEDIFF(mi, t_on.OnTime, t_off.OffTime)) AS TotalRunMinutes
FROM
    -- 筛选并编号所有通电开始记录
    (SELECT 
         OnTime,
         ROW_NUMBER() OVER (ORDER BY OnTime) AS RowNum
     FROM YourTableName
     WHERE OnTime IS NOT NULL) AS t_on
JOIN
    -- 筛选并编号所有通电结束记录
    (SELECT 
         OffTime,
         ROW_NUMBER() OVER (ORDER BY OffTime) AS RowNum
     FROM YourTableName
     WHERE OffTime IS NOT NULL) AS t_off
ON t_on.RowNum = t_off.RowNum
-- 添加时间范围筛选，只统计完全在指定区间内的通电时段
WHERE t_on.OnTime >= @StartDate 
  AND t_off.OffTime <= @EndDate;

关键细节说明 #

• 配对逻辑：ROW_NUMBER()窗口函数会按时间顺序给开/关记录分别编号，确保每一条开记录对应紧随其后的关记录，完全匹配你的数据插入逻辑。
• 时长计算：DATEDIFF(mi, ...)直接返回两个时间之间的分钟数，SUM()函数会自动将所有组的分钟数累加，这就是你需要的总时长。
• 时间范围：通过WHERE子句限制只有完全落在@StartDate和@EndDate之间的时段才会被统计，如果你需要统计部分重叠的时段，可以调整条件为t_on.OnTime <= @EndDate AND t_off.OffTime >= @StartDate。

示例数据验证 #

用你给出的示例数据运行这个查询，会得到120分钟的结果——第一组（ID1和ID2）是60分钟，第二组（ID3和ID4）是60分钟，总和正好符合你的期望。

补充：处理未关闭的通电记录 #

如果存在最后一条记录是通电开始（OnTime有值，OffTime为null）的情况，这个查询会自动忽略它（因为没有对应的关记录）。如果需要把这部分未结束的时长计算到当前时间，可以修改为LEFT JOIN，并在计算时用ISNULL(t_off.OffTime, GETDATE())代替t_off.OffTime，比如：

SELECT 
    SUM(DATEDIFF(mi, t_on.OnTime, ISNULL(t_off.OffTime, GETDATE()))) AS TotalRunMinutes
FROM
    (SELECT OnTime, ROW_NUMBER() OVER (ORDER BY OnTime) AS RowNum FROM YourTableName WHERE OnTime IS NOT NULL) AS t_on
LEFT JOIN
    (SELECT OffTime, ROW_NUMBER() OVER (ORDER BY OffTime) AS RowNum FROM YourTableName WHERE OffTime IS NOT NULL) AS t_off
ON t_on.RowNum = t_off.RowNum
WHERE t_on.OnTime >= @StartDate;

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