sql查询，将单元格值设置为另一列中另一个单元格之间的差减去先前的结果。

Question

我尝试了以下查询，但它引发了错误，

当子查询未引入时，只能在select列表中指定一个表达式

UPDATE dbo.SFData 
SET KPIperMonth =
    (SELECT KPIValue, KPIValue - LAG(KPIValue) OVER (ORDER BY SFID) PreviousValue
     from dbo.SFData)

我有一张桌子如下

SFID|KPIValue|KPIperMonth
---------------------
1   |1       |
2   |3       |
3   |5       |

上表显示了以月份KPIValue为单位的销售累计总和。我希望这些结果：

SFID|KPIValue|KPIperMonth
---------------------
1   |1       |1
2   |3       |2 (3-1)
3   |5       |2 (5-3)

Answer 1

试试这个：

WITH Previous_KPI AS
(
    SELECT SFID, KPIValue, KPIValue - LAG(KPIValue,1,0) OVER (ORDER BY SFID) PreviousValue
    FROM SFData
)

UPDATE 
    SFData
SET 
    SFData.KPIperMonth = Previous_KPI.PreviousValue
FROM
    SFData 
    LEFT JOIN Previous_KPI
        ON SFData.SFID = Previous_KPI.SFID