这条sql语句该如何优化呢

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

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
[解决办法]
探讨


求帮助呀！
 

 

查看更多 下一篇