- SQL code
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 endelse0end,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 endelse0end,user.jyan=user.jyan+(case when footballrec.betcoin >=50000 and footballrec.betcoin <100000 then1when footballrec.betcoin >=100000 and footballrec.betcoin <200000 then2when footballrec.betcoin >=200000 and footballrec.betcoin <500000 then5when footballrec.betcoin >=500000 and footballrec.betcoin <1000000 then10when footballrec.betcoin >=1000000 then15else0end) 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=这一期游戏的期号
现在应该怎么优化这条语句呢，先谢过大家了！！
------解决方案--------------------
不说表结构设计问题。从语句来说，你更新可以分两次更新试试。索引方面不能提供
------解决方案--------------------
- SQL code
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 = @bnuminner join a on a.Auserid = m.id
------解决方案--------------------