我先说明一下数据库和要完成的结果
字段名 IDX A B C
7 100 0 ?
40 100 100 ?
58 0 100 ?
85 200 0 ?
115 200 250 ?
145 150 0 ?
154 100 50 ?要的结果就是上一条记录的C和当前记录+A-B得C, 就是一个进帐的余额累加我写了一个比较笨的语句,执行后为什么每次都只计算一条,上面7条记录得按7下才能出结果。
大家帮我看看有没有什么问题。
UPDATE A
SET 余额 = CAST(ISNULL
((SELECT B.余额
FROM TZ200812 B
WHERE B.IDX = CAST
((SELECT MAX(C.Idx)
FROM TZ200812 C
WHERE (A.Idx > C.Idx)) AS int)), 0) AS int) + 借方额 - 贷方额
FROM TZ200812 A
字段名 IDX A B C
7 100 0 ?
40 100 100 ?
58 0 100 ?
85 200 0 ?
115 200 250 ?
145 150 0 ?
154 100 50 ?要的结果就是上一条记录的C和当前记录+A-B得C, 就是一个进帐的余额累加我写了一个比较笨的语句,执行后为什么每次都只计算一条,上面7条记录得按7下才能出结果。
大家帮我看看有没有什么问题。
UPDATE A
SET 余额 = CAST(ISNULL
((SELECT B.余额
FROM TZ200812 B
WHERE B.IDX = CAST
((SELECT MAX(C.Idx)
FROM TZ200812 C
WHERE (A.Idx > C.Idx)) AS int)), 0) AS int) + 借方额 - 贷方额
FROM TZ200812 A
-- Author: liangCK 小梁
-- Date : 2008-11-19 20:29:55
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (IDX INT,A INT,B INT,C INT)
INSERT INTO @T
SELECT 7,100,0,null UNION ALL
SELECT 40,100,100,null UNION ALL
SELECT 58,0,100,null UNION ALL
SELECT 85,200,0,null UNION ALL
SELECT 115,200,250,null UNION ALL
SELECT 145,150,0,null UNION ALL
SELECT 154,100,50,null--SQL查询如下:DECLARE @c INT
SET @c=0UPDATE A SET
@c=@c+A-B,
C=@c
FROM @T AS ASELECT * FROM @T/*
IDX A B C
----------- ----------- ----------- -----------
7 100 0 100
40 100 100 100
58 0 100 0
85 200 0 200
115 200 250 150
145 150 0 300
154 100 50 350(7 行受影响)
*/
INSERT INTO tb
SELECT 7,100,0,null UNION ALL
SELECT 40,100,100,null UNION ALL
SELECT 58,0,100,null UNION ALL
SELECT 85,200,0,null UNION ALL
SELECT 115,200,250,null UNION ALL
SELECT 145,150,0,null UNION ALL
SELECT 154,100,50,null
go
update tb
set C=b.C
from (select IDX,C=(select isnull(sum(A-B),0) from tb where IDX<=a.IDX) from tb a) b
where tb.IDX=b.IDXselect * from tb
/*
IDX A B C
----------- ----------- ----------- -----------
7 100 0 100
40 100 100 100
58 0 100 0
85 200 0 200
115 200 250 150
145 150 0 300
154 100 50 350(7 行受影响)
*/