SQL求助：如何对比行间Column A值并递增Column B？

需求是针对表中Column A（smallint类型），对比当前行与上一行的Column A值：

• 若当前行Column A值大于上一行，Column B递增1
• 同时计算DiffValue为当前行Column A与上一行的差值（第一行差值为0）

执行前数据表 #

执行后预期数据表 #

现有代码（存在瓶颈） #

UPDATE tA
SET tA.DiffValue =tB.DiffValue,
FROM MyTable as tA
INNER JOIN(
        SELECT Id, DiffValue = A - LAG(A) OVER (ORDER BY A ASC)
        FROM MyTable 
        ) as tB ON tA.Id = tB.Id

可行解决方案 #

你需要同时计算DiffValue和累计递增的Column B，可以用窗口函数一次性计算出所需值，再执行更新。以下是完整的SQL语句：

步骤1：先查询验证结果 #

先通过SELECT语句确认计算逻辑是否符合预期：

SELECT 
    Id,
    [Column A],
    -- 累计满足A>上一行A的次数，第一行默认0
    SUM(CASE WHEN [Column A] > LAG([Column A]) OVER (ORDER BY Id) THEN 1 ELSE 0 END) 
        OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Column B],
    -- 计算当前行与上一行的差值，第一行设为0
    ISNULL([Column A] - LAG([Column A]) OVER (ORDER BY Id), 0) AS DiffValue
FROM MyTable
ORDER BY Id;

步骤2：执行更新操作 #

如果查询结果符合预期，执行以下UPDATE语句：

WITH UpdatedValues AS (
    SELECT 
        Id,
        SUM(CASE WHEN [Column A] > LAG([Column A]) OVER (ORDER BY Id) THEN 1 ELSE 0 END) 
            OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NewColumnB,
        ISNULL([Column A] - LAG([Column A]) OVER (ORDER BY Id), 0) AS NewDiffValue
    FROM MyTable
)
UPDATE MyTable
SET 
    [Column B] = uv.NewColumnB,
    DiffValue = uv.NewDiffValue
FROM MyTable t
JOIN UpdatedValues uv ON t.Id = uv.Id;

关键逻辑说明 #

• LAG([Column A]) OVER (ORDER BY Id)：必须按Id排序来获取"上一行"值，若按Column A排序会打乱原始行顺序，不符合需求
• SUM(...) OVER (ORDER BY Id)：通过累计满足条件的次数，实现Column B的递增逻辑
• ISNULL(..., 0)：处理第一行无前置行的特殊情况，将DiffValue设为0

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