发现一段经典SQL，不用循环游标，一句update代码实现滚动计算结存。为方便理解，结合实例测试之
--1，源数据#t1，jcshl初值为每个sid的当前库存数量，要实现的效果：每个sid的后一结存数量为前一jcshl结存数量-chkshl出库数量
SELECT * FROM #t1 ORDER BY sn
sn plh sid chkshl jcshl
1 S000220.0000980.0000
2 S000310.00001010.0000
3 S000310.00001010.0000
4 S000310.00001010.0000
5 S000210.0000980.0000
6 S00021.0000980.0000
7 S000420.0000720.0000
8 S000510.0000530.0000
9 S000510.0000530.0000
10 S000510.0000530.0000
11 S000410.0000720.0000
12 S00041.0000720.0000
--2，按sid排序#t2，数据顺序决定分组及计算顺序
SELECT * INTO #t2 FROM #t1 ORDER BY sid,sn
sn plh sid chkshl jcshl
1 S000220.0000980.0000
5 S000210.0000980.0000
6 S00021.0000980.0000
2 S000310.00001010.0000
3 S000310.00001010.0000
4 S000310.00001010.0000
7 S000420.0000720.0000
11 S000410.0000720.0000
12 S00041.0000720.0000
8 S000510.0000530.0000
9 S000510.0000530.0000
10 S000510.0000530.0000
--3，滚动更新jcshl结存数量，同时填入新的plh排列号
DECLARE @plh CHAR(11),@jcshl DECIMAL(18,4),@sid CHAR(11)UPDATE #t2 SET
@jcshl=jcshl=CASE WHEN sid=@sid THEN @jcshl-chkshl ELSE jcshl-chkshl END,
@plh=plh=STR(ISNULL(@plh,0))+1,
@sid=sid=sidSELECT * FROM #t2
sn plh sid chkshl jcshl
11 S000220.0000960.0000
52 S000210.0000950.0000
63 S00021.0000949.0000
24 S000310.00001000.0000
35 S000310.0000990.0000
46 S000310.0000980.0000
77 S000420.0000700.0000
118 S000410.0000690.0000
129 S00041.0000689.0000
810 S000510.0000520.0000
911 S000510.0000510.0000
1012 S000510.0000500.0000
