SQL Server：基于FuelIndicator列的特定切换场景累计值计算

我来帮你搞定这个需求——当FuelIndicator从Not Reserve切换到Reserve时，把切换前连续的Not Reserve记录的FuelAmt累加至切换后的第一条Reserve记录里，同时移除所有Not Reserve记录。我们可以用SQL Server的窗口函数来优雅实现这个逻辑，下面是具体步骤和代码：

实现思路 #

1. 标记连续的Not Reserve分组：用LAG和LEAD窗口函数识别连续的Not Reserve块，以及这些块之后的第一条Reserve记录。
2. 计算分组累计值：对每个连续的Not Reserve块计算FuelAmt的总和，并关联到对应的目标Reserve记录。
3. 合并生成最终结果：保留所有Reserve记录，将对应分组的累计值加到目标Reserve的FuelAmt上，过滤掉Not Reserve记录。

完整SQL代码 #

-- 替换YourTableName为你的实际表名
WITH CTE_Grouped AS (
    -- 第一步：标记连续NotReserve的分组，以及切换到Reserve的节点
    SELECT 
        *,
        -- 标记当前行是否是连续NotReserve块的起始行
        CASE 
            WHEN FuelIndicator = 'Not Reserve' 
                 AND LAG(FuelIndicator, 1, '') OVER (ORDER BY FuelDate) != 'Not Reserve'
            THEN 1 
            ELSE 0 
        END AS IsStartOfNotReserve,
        -- 标记当前行是否是NotReserve块的最后一行，且下一行是Reserve
        CASE 
            WHEN FuelIndicator = 'Not Reserve' 
                 AND LEAD(FuelIndicator, 1, '') OVER (ORDER BY FuelDate) = 'Reserve'
            THEN 1 
            ELSE 0 
        END AS IsEndOfNotReserve,
        -- 给每个连续的NotReserve块分配唯一组ID
        SUM(CASE 
                WHEN FuelIndicator = 'Not Reserve' 
                     AND LAG(FuelIndicator,1,'') OVER (ORDER BY FuelDate) != 'Not Reserve'
                THEN 1 
                ELSE 0 
            END) OVER (ORDER BY FuelDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NotReserveGroupID
    FROM YourTableName
),
CTE_NotReserveTotals AS (
    -- 第二步：计算每个NotReserve组的总FuelAmt，以及对应的目标Reserve记录日期
    SELECT 
        NotReserveGroupID,
        SUM(FuelAmt) AS TotalNotReserveFuel,
        -- 获取该组之后的第一条Reserve记录的日期
        MIN(LEAD(FuelDate, 1) OVER (PARTITION BY NotReserveGroupID ORDER BY FuelDate)) AS TargetReserveDate
    FROM CTE_Grouped
    WHERE FuelIndicator = 'Not Reserve'
    GROUP BY NotReserveGroupID
)
-- 第三步：合并结果，累加对应的值并保留Reserve记录
SELECT 
    t.FuelDate,
    t.FilledBy,
    t.KMSreading,
    -- 对目标Reserve记录累加NotReserve的总和，其他Reserve保留原值
    t.FuelAmt + ISNULL(nrt.TotalNotReserveFuel, 0) AS FuelAmt,
    t.FuelPrice,
    t.Vehicle,
    t.FuelIndicator
FROM YourTableName t
LEFT JOIN CTE_NotReserveTotals nrt 
    ON t.FuelDate = nrt.TargetReserveDate
WHERE t.FuelIndicator = 'Reserve' -- 仅保留Reserve类型的记录
ORDER BY t.FuelDate;

代码说明 #

• CTE_Grouped：通过窗口函数识别连续的Not Reserve块，用NotReserveGroupID将同一连续块的记录归为一组，同时标记出块的结束节点（即下一行是Reserve的Not Reserve记录）。
• CTE_NotReserveTotals：计算每个NotReserve组的FuelAmt总和，并找到该组之后的第一条Reserve记录的日期，作为累加的目标。
• 最终查询：左连接两个CTE，将目标Reserve记录的FuelAmt加上对应组的总和，同时只保留Reserve类型的记录，完美匹配你的预期输出。

用你的示例数据测试这段代码，会得到和你给出的预期结果完全一致的输出。

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