首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

哪位高手能帮小弟我把这个游标优化下

2012-01-21 
谁能帮我把这个游标优化下declare @username1 nvarchar(50),@uermoney1 int//定义下用户名declare uname c

谁能帮我把这个游标优化下
declare @username1 nvarchar(50),@uermoney1 int //定义下用户名
declare uname cursor for select username,uermoney from users //从用户表查用户名和金额放到游标里
open uname
fetch next from uname into @username1,@uermoney1 //把游标查询放到两个变量里
while @@FETCH_STATUS=0
begin
select @username1,@uermoney1,b_after from bank_back where @uermoney1<>
(select (select sum(back_money2)-sum(back_money) from bank_back where username=@username1 and addtime>'11-11-1 17:47'
and follows not like '%hedui%' and follows not like '修改资金')+(select top 1 b_after from bank_back where username=@username1 and addtime<'11-11-1 17:47' order by addtime desc)
//这个是计算他应当有的资金. 就是从<>到这个括号这边,这个不用去改,也不用管怎样算的。
)//这个查询就是要比对用户表里的资金跟他应当有的资金是否一致,如果不一致则查出来。

fetch next from uname into @username1,@uermoney1
end
close unamen
deallocate uname

我期待的结果是,在一张临时表里,有三个列,用户名和用户资金和他实际应当的资金。然后如果用户资金和他实际资金不一致则列出来。但是我查询出来结果是
0 行受影响)
...
...
...
...
(0 行受影响)

(0 行受影响)

(202605 行受影响)

(202605 行受影响)

(0 行受影响)

...
(0 行受影响)


[解决办法]
中間這段這樣改試試

SQL code
select @username1,@uermoney1,b_after from bank_back  AS a    INNER JOIN (select top 1 b_after from bank_back where username=@username1 and addtime<'11-11-1 17:47' order by addtime desc) AS b ON a.username=b.username    INNER JOIN (select sum(back_money2)-sum(back_money) AS back_money from bank_back where username=@username1 and addtime>'11-11-1 17:47' and follows not like '%hedui%' and follows not like '修改资金') AS c ON c.username=a.usernameWHERE b.b_after <>@uermoney1-c.back_money
[解决办法]
直接查询不行吗

SQL code
select a.username,a.uermoney1,b.b_after from users  a,bank_back b where a.username=b.usernameand uermoney1<>(select (select sum(back_money2)-sum(back_money) from bank_back where username=b.username1 and addtime>'11-11-1 17:47'and follows not like '%hedui%' and follows not like '修改资金')+(select top 1 b_after from bank_back where username=b.username1 and addtime<'11-11-1 17:47' order by addtime desc) 

热点排行