这条sql语句该怎么优化呢？

[hunren]

update user set user.coin=user.coin+ footballrec.getcoin,user.jifen=user.jifen
+
case when not EXISTS(select id from A where Auserid=user.id) then
	case when footballrec.getcoin - footballrec.betcoin >=1000 and footballrec.getcoin - footballrec.betcoin <10000 then
	1
	when footballrec.getcoin - footballrec.betcoin >=10000 and footballrec.getcoin - footballrec.betcoin <50000 then
	2
	when footballrec.getcoin - footballrec.betcoin >=50000 then
	3
	else
	0
	end
else
0
end
,user.jifenall=user.jifenall+
case when not EXISTS(select id from A where Auserid=user.id) then
	case when footballrec.getcoin - footballrec.betcoin >=1000 and footballrec.getcoin - footballrec.betcoin <10000 then
	1
	when footballrec.getcoin - footballrec.betcoin >=10000 and footballrec.getcoin - footballrec.betcoin <50000 then
	2
	when footballrec.getcoin - footballrec.betcoin >=50000 then
	3
	else
	0
	end
else
0
end
,user.jyan=user.jyan+(
case when footballrec.betcoin >=50000 and footballrec.betcoin <100000 then
1
when footballrec.betcoin >=100000 and footballrec.betcoin <200000 then
2
when footballrec.betcoin >=200000 and footballrec.betcoin <500000 then
5
when footballrec.betcoin >=500000 and footballrec.betcoin <1000000 then
10
when footballrec.betcoin >=1000000 then
15
else
0
end
) from user inner join footballrec on user.id=footballrec.userid where footballrec.betnum= + @bnum

作用是根据竞猜表(footballrec)中的投注记录更新用户表(user)里用户的“金币”，“积分”，“总积分”，“经验值”。
user表中的几个字段：
id=用户的ID
coin=用户的金币
jifen=用户的积分
jifenall=用户的总积分
jyan=用户的经验

footballrec表中的几个字段：
userid=用户ID
betconi=用户在这一期的投注金币
getcoin=用户在这一期的获得金币
betnum=这一期游戏的期号

现在应该怎么优化这条语句呢，先谢过大家了！！

[SQL777]

不说表结构设计问题。从语句来说，你更新可以分两次更新试试。索引方面不能提供

[dawugui]

update [user] set 
coin  = m.coin + n.getcoin,
jifen = m.jifen
+
(
    case when n.getcoin - n.betcoin >= 1000 and n.getcoin - n.betcoin < 10000 then 1
         when n.getcoin - n.betcoin >= 10000 and n.getcoin - n.betcoin < 50000 then 2
         when n.getcoin - n.betcoin >= 50000 then 3
         else 0
    end
),
jifenall = m.jifenall + 
(
    case when n.getcoin - n.betcoin >= 1000 and n.getcoin - n.betcoin < 10000 then 1
         when n.getcoin - n.betcoin >= 10000 and n.getcoin - n.betcoin < 50000 then 2
         when n.getcoin - n.betcoin >= 50000 then 3
         else 0
    end
),
jyan = m.jyan +
(
    case when n.betcoin >= 50000 and n.betcoin < 100000 then 1
         when n.betcoin >= 100000 and n.betcoin < 200000 then 2
         when n.betcoin >= 200000 and n.betcoin < 500000 then 5
         when n.betcoin >= 500000 and n.betcoin < 1000000 then 10
         when n.betcoin >= 1000000 then 15
         else 0
    end
) 
from [user] m 
inner join footballrec n on m.id = n.userid and n.betnum = @bnum
inner join a on a.Auserid = m.id

[hunren]

感谢二位！
爱新觉罗.毓华：
“经验”这列不用判断A表哦

SQL777：
请教应该怎样分两次更新呢？然后对表结构设计有啥建议呢？

另外，user表中id是主键，footballrec中userid和betnum是索引，以上的语句会引起全表扫描吗？还有锁会是什么类型的呢？

[开着拖拉机泡妞]

[Quote=引用 4 楼 hunren 的回复:]

求帮助呀！
[/Quote]

建议你提供测试数据和结果，方便别人直接给你实现你的要求

[hunren]

求帮助呀！