mysql的sql筛选排重最大值并修改其属性

 

修改属性

mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; update user_info set isActive=0 where 1=1"

mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; update user_info set isActive=1 where userId in (select r.userId from role r join role s on r.userName=s.userName where r.lv>s.lv group by r.lv)"

mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; update user_info set isActive=1 where userName not in (select * from (select userName from user_info where isActive=1) as temp)"

 

 

验证修改
mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; select r.userId,r.userName, r.lv, r.vipLv,r.roleName from role r where r.userName='g324'"
mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; select * from user_info where userName='g324'"

mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; select r.userId,r.userName, r.lv, r.vipLv,r.roleName from role r where r.userName='d755d7f29a679a11c235cf8ff0b20b45'"
mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; select * from user_info where userName='d755d7f29a679a11c235cf8ff0b20b45'"

mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; select r.userId,r.userName, r.lv, r.vipLv,r.roleName from role r where r.userName='ae47ebbec25cd60f'"
mysql -h192.168.1.51 -uroot -e "use codex_game_s1051_h; select * from user_info where userName='ae47ebbec25cd60f'"