如上图所示:
--需求:A2的值为A1与A2同行的上一行值-本行值
SELECT AX.A3,AX.A4,AX.A1,CASE WHEN ISNULL(B.A1,0)=0 THEN 0 ELSE AX.A1-B.A1 END AS A2 FROM A AX OUTER APPLY
(SELECT A1 FROM A WHERE A3=AX.A3 AND A4-1=AX.A4) B ORDER BY A4 DESC
效果图如下:
如上图所示:
--需求:A2的值为A1与A2同行的上一行值-本行值
SELECT AX.A3,AX.A4,AX.A1,CASE WHEN ISNULL(B.A1,0)=0 THEN 0 ELSE AX.A1-B.A1 END AS A2 FROM A AX OUTER APPLY
(SELECT A1 FROM A WHERE A3=AX.A3 AND A4-1=AX.A4) B ORDER BY A4 DESC
效果图如下: