如何在SQL Server 2017中计算两行同字段的差值？

原表数据 #

预期结果 #

实现代码（SQL Server 2017） #

利用LAG()窗口函数直接获取上一行的字段值，再通过ISNULL()处理首行的空值：

SELECT 
    MRLNum,
    CreatedOn,
    ReviseCode,
    RequiredQty,
    ISNULL(CAST(LAG(ReviseCode) OVER (ORDER BY ReviseCode) AS VARCHAR(10)), 'NA') AS ReviseCodePrvDiff,
    ISNULL(CAST(LAG(RequiredQty) OVER (ORDER BY ReviseCode) AS VARCHAR(10)), 'NA') AS RequiredQtyPrvDiff
FROM 
    YourTableName
ORDER BY 
    ReviseCode;

分组计算说明 #

如果表中存在多个MRLNum条目，需要按MRLNum分组计算组内上一行差值，修改OVER()子句添加PARTITION BY：

SELECT 
    MRLNum,
    CreatedOn,
    ReviseCode,
    RequiredQty,
    ISNULL(CAST(LAG(ReviseCode) OVER (PARTITION BY MRLNum ORDER BY ReviseCode) AS VARCHAR(10)), 'NA') AS ReviseCodePrvDiff,
    ISNULL(CAST(LAG(RequiredQty) OVER (PARTITION BY MRLNum ORDER BY ReviseCode) AS VARCHAR(10)), 'NA') AS RequiredQtyPrvDiff
FROM 
    YourTableName
ORDER BY 
    MRLNum, ReviseCode;

函数解释 #

• LAG(column) OVER (ORDER BY sort_column)：按指定排序规则，获取当前行的上一行目标列值，首行返回NULL。
• ISNULL(CAST(...) AS VARCHAR(10)), 'NA')：将空值转换为字符串'NA'，匹配预期结果格式。

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