declare @LastYe money
set @LastYe = 0
update TmpAccount set @LastYe = @LastYe + (mDebit - mCredit),mBanlance = @LastYe
TmpAccount表有主键,有一个ID列是主键。为什么这样更新后mBanlance 结果总是不定呢?
set @LastYe = 0
update TmpAccount set @LastYe = @LastYe + (mDebit - mCredit),mBanlance = @LastYe
TmpAccount表有主键,有一个ID列是主键。为什么这样更新后mBanlance 结果总是不定呢?
解决方案 »
- SQL 语句格式的问题
- 如何将SQL服务器连接(主机名\实例名)更改为只需要用local来访问本地服务器?
- 关于COALESCE取值的问题
- 求教sql2005数据库附加报错问题(数据库修复)
- 表性能问题。重建索引超时,紧急求助。
- 一个简单的SQL问题...
- osql调用外部*.sql文件再远程数据库上创建表失败
- 问一个关于存储过程参数的问题
- 大哥们给我看看下面个那里错了!
- 高分求救!如何建立一个存储过程能检测一个数据库中所有表中的字段是否为空值及合法性?
- 查询组合问题
- SQL语句: where tname='aa' or tname='bb' 和 where tname in ('aa','bb') 哪个效率高?
iID int IDENTITY (1, 1) ,
mDebit money,
mCredit money,
mBanlance money)iID mDebit mCredit mBanlance
----------- --------------------- --------------------- ---------------------
1 20000.0000 .0000 20000.0000
2 11115.7000 .0000 31115.7000
3 8673.4000 .0000 39789.1000
4 9041.5000 .0000 48830.6000
5 25297.8000 .0000 74128.4000
6 73838.1800 .0000 147966.5800
7 384430.0900 .0000 532396.6700
8 40622.6100 .0000 573019.2800
9 198973.5600 .0000 771992.8400
10 350579.6400 .0000 1122572.4800
11 3148013.4500 .0000 4270585.9300
12 1785807.7900 .0000 6056393.7200
13 173966.1800 .0000 6230359.9000
14 34323.0500 .0000 6264682.9500
15 534763.6500 .0000 6799446.6000
16 544445.0900 .0000 7343891.6900
17 496244.7700 .0000 7840136.4600
18 280845.0000 .0000 8120981.4600
19 1000.0000 .0000 8121981.4600
20 191655.0000 .0000 8313636.4600
21 594980.1900 .0000 8908616.6500
22 485091.2600 .0000 9393707.9100
23 498903.9400 .0000 9892611.8500
24 5200.0000 .0000 9897811.8500
25 10000.0000 .0000 9907811.8500
26 11000.0000 .0000 9918811.8500
27 15000.0000 .0000 9933811.8500
28 3000.0000 .0000 9936811.8500
29 5306.0000 .0000 9942117.8500
30 2653.0000 .0000 9944770.8500
31 3630.0000 .0000 9948400.8500
32 1980.0000 .0000 9950380.8500
33 2740.0000 .0000 9953120.8500
34 2300.0000 .0000 9955420.8500
35 3000.0000 .0000 9958420.8500
36 2300.0000 .0000 9960720.8500
update TmpAccount SET mBanlance = @LastYe + (mDebit - mCredit)
和事务有关系么?我总共就只有一条词句啊!
set
mBanlance=isnull((select sum(mDebit-mCredit) from TmpAccount where ID<t.ID),0)
from
TmpAccount t
set
mBanlance=isnull((select sum(mDebit-mCredit) from TmpAccount where ID<=t.ID),0)
from
TmpAccount t
declare @LastYe money
set @LastYe = 0
update TmpAccount set @LastYe = mBanlance = @LastYe + (mDebit - mCredit)同时:你的SQLSERVER 打补丁了没?? ID确认是主键?
iID int IDENTITY (1, 1) PRIMARY KEY,<<<--
mDebit money,
mCredit money,
mBanlance money)
declare @LastYe money
set @LastYe = 0 Update a
Set @LastYe = @LastYe + (mDebit - mCredit),mBanlance = @LastYe
From
(
Select * From TmpAccount Order By ID
) a
declare @LastYe money
set @LastYe = 0
update A set
@LastYe = @LastYe + (mDebit - mCredit),mBanlance = @LastYe
FROM TmpAccount WITH(INDEX=PK_TmpAccount) -- 注意把 PK_TmpAccount 换成你的主键名称(注意不是列名)
(这个问题我的书上有讲, 并且有示例说明此问题)
===========
是的,当时没有测试环境,没有测试 :)
用With指定索引的方法可以以更好的速度实现。