不用循环游标，一句update代码实现滚动计算

发现一段经典SQL，不用循环游标，一句update代码实现滚动计算结存。为方便理解，结合实例测试之

--1，源数据#t1，jcshl初值为每个sid的当前库存数量
--要实现的效果：每个sid当前行结存数量等于前一行结存数量jcshl减去当前行出库数量chkshl

--CREATE TABLE #t1 (
--	sn INT NOT NULL,
--	sid CHAR(5),
--	chkshl DECIMAL(18,4),
--	jcshl DECIMAL(18,4),
--	plh INT
--)

SELECT * FROM #t1 ORDER BY sn

sn	sid	chkshl	jcshl	plh
1	S0002	20.0000	980.0000
2	S0003	10.0000	1010.0000
3	S0003	10.0000	1010.0000
4	S0003	10.0000	1010.0000
5	S0002	10.0000	980.0000
6	S0002	1.0000	980.0000
7	S0004	20.0000	720.0000
8	S0005	10.0000	530.0000
9	S0005	10.0000	530.0000
10	S0005	10.0000	530.0000
11	S0004	10.0000	720.0000
12	S0004	1.0000	720.0000

--2，按sid排序#t2，数据顺序决定分组及计算顺序

SELECT * INTO #t2 FROM #t1 ORDER BY sid,sn

sn	sid	chkshl	jcshl	plh
1	S0002	20.0000	980.0000
5	S0002	10.0000	980.0000
6	S0002	1.0000	980.0000
2	S0003	10.0000	1010.0000
3	S0003	10.0000	1010.0000
4	S0003	10.0000	1010.0000
7	S0004	20.0000	720.0000
11	S0004	10.0000	720.0000
12	S0004	1.0000	720.0000
8	S0005	10.0000	530.0000
9	S0005	10.0000	530.0000
10	S0005	10.0000	530.0000

--3，滚动更新jcshl结存数量，同时填入新的plh排列号

DECLARE @plh int,@jcshl DECIMAL(18,4),@sid CHAR(5)

UPDATE #t2 SET 
    @jcshl=jcshl=CASE WHEN sid=@sid THEN @jcshl-chkshl ELSE jcshl-chkshl END,
    @plh=plh=ISNULL(@plh,0)+1,
    @sid=sid=sid

SELECT * FROM #t2

sn	sid	chkshl	jcshl	plh
1	S0002	20.0000	960.0000	1
5	S0002	10.0000	950.0000	2
6	S0002	1.0000	949.0000	3
2	S0003	10.0000	1000.0000	4
3	S0003	10.0000	990.0000	5
4	S0003	10.0000	980.0000	6
7	S0004	20.0000	700.0000	7
11	S0004	10.0000	690.0000	8
12	S0004	1.0000	689.0000	9
8	S0005	10.0000	520.0000	10
9	S0005	10.0000	510.0000	11
10	S0005	10.0000	500.0000	12