不用循环游标,一句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
posted @ 2020-01-16 15:56  hhlotus  阅读(553)  评论(0编辑  收藏  举报