如何用SQL将同ID下B类型超10小时的时长扣减后叠加到下一行

核心思路是先通过窗口函数给同ID的行按时间排序，再关联上一行的扣减时长，分别调整当前行的开始和结束时间：

完整SQL代码（适配SQL Server，其他数据库可对应修改时间函数） #

WITH row_order AS (
    -- 给同ID的行按开始时间排序，方便关联上一行
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY StartTime) AS rn,
        -- 计算当前行原始时长
        DATEDIFF(HOUR, StartTime, EndTime) AS origin_hour,
        -- 计算当前行需要扣减的时长（只有Type为B且超10小时才扣，否则为0）
        CASE 
            WHEN Type = 'B' AND DATEDIFF(HOUR, StartTime, EndTime) > 10 
            THEN DATEDIFF(HOUR, StartTime, EndTime) - 10 
            ELSE 0 
        END AS cut_hour
    FROM 你的表名
),
cut_calc AS (
    SELECT 
        *,
        -- 取上一行的扣减时长，用来调整当前行的开始时间
        LAG(cut_hour,1,0) OVER(PARTITION BY ID ORDER BY rn) AS prev_cut
    FROM row_order
)
SELECT 
    ID,
    -- 当前行开始时间 = 原始开始时间减去上一行的扣减时长
    DATEADD(HOUR, -prev_cut, StartTime) AS StartTime,
    -- 当前行结束时间 = 原始结束时间减去当前行的扣减时长
    DATEADD(HOUR, -cut_hour, EndTime) AS EndTime,
    Type,
    Rate,
    Location
FROM cut_calc

逻辑说明 #

• 先给同ID的所有行按StartTime升序排序生成行号，保证顺序和业务上的先后一致
• 预计算每行需要扣减的时长：只有Type为B且原始时长超过10小时的行才会生成大于0的扣减值，其余行扣减值为0
• 用LAG窗口函数取同ID上一行的扣减值，调整当前行的开始时间，刚好承接上一行多扣的时长
• 除了StartTime和EndTime，其余列直接取原始值，符合需求要求
  你写的原始SQL的问题是只调整了当前行的EndTime，没有关联上一行数据来调整当前行的StartTime，也没有正确计算扣减的时长偏移量。

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