---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (月份 INT,期初 INT,加项 INT,减项 INT,余额 INT)
INSERT INTO @T
SELECT 200801,100,0,0,100 UNION ALL
SELECT 200802,0,400,200,0 UNION ALL
SELECT 200803,0,500,100,0 UNION ALL
SELECT 200804,0,200,300,0 UNION ALL
SELECT 200805,0,100,600,0--SQL查询如下:DECLARE @余额 INT;
SET @余额=0;UPDATE @T SET
@余额=@余额+期初+加项-减项,
余额=@余额SELECT * FROM @T;/*
月份 期初 加项 减项 余额
----------- ----------- ----------- ----------- -----------
200801 100 0 0 100
200802 0 400 200 300
200803 0 500 100 700
200804 0 200 300 600
200805 0 100 600 100(5 行受影响)
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (月份 INT,期初 INT,加项 INT,减项 INT,余额 INT)
INSERT INTO @T
SELECT 200801,100,0,0,100 UNION ALL
SELECT 200802,0,400,200,0 UNION ALL
SELECT 200803,0,500,100,0 UNION ALL
SELECT 200804,0,200,300,0 UNION ALL
SELECT 200805,0,100,600,0--SQL查询如下:DECLARE @余额 INT;
SET @余额=0;UPDATE @T SET
@余额=@余额+期初+加项-减项,
余额=@余额SELECT * FROM @T;/*
月份 期初 加项 减项 余额
----------- ----------- ----------- ----------- -----------
200801 100 0 0 100
200802 0 400 200 300
200803 0 500 100 700
200804 0 200 300 600
200805 0 100 600 100(5 行受影响)
*/
insert @tb select '200802', 0 ,400 ,200, 0
insert @tb select '200803', 0, 500 ,100, 0
insert @tb select '200804', 0 ,200, 300, 0
insert @tb select '200805', 0 ,100 ,600 ,0 declare @num int
set @num=0
update @tb set 余额=@num,@num=@num+期初+加项-减项select * from @tb/*
月份 期初 加项 减项 余额
------ ----------- ----------- ----------- -----------
200801 100 0 0 100
200802 0 400 200 300
200803 0 500 100 700
200804 0 200 300 600
200805 0 100 600 100(所影响的行数为 5 行)
*/
你有几千万条跟我无关..
我的只是测试数据..
你将我UPDATE语句中的@T表,换成你几千万条记录的表不就成了吗?
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (月份 INT,期初 INT,加项 INT,减项 INT,余额 INT)
INSERT INTO @T
SELECT 200801,100,0,0,100 UNION ALL
SELECT 200802,0,400,200,0 UNION ALL
SELECT 200803,0,500,100,0 UNION ALL
SELECT 200804,0,200,300,0 UNION ALL
SELECT 200805,0,100,600,0--SQL查询如下:SELECT
ISNULL((SELECT SUM(期初+加项-减项) FROM @T
WHERE 月份<A.月份),0)+期初+加项-减项
FROM @T AS A/*
-----------
100
300
700
600
100(5 行受影响)*/