How to update win/lose/draw based on percentage?-CodePudding

I wanna ask.

How to find win/lose/draw player based on win rate percentage like this image ?

 ---- ----------- --------- -------- --------------------- 
| id | battle_id | user_id | status | win_rate_percentage |
 ---- ----------- --------- -------- --------------------- 
| 13 |         7 |     542 | lost   |               18.00 |
| 14 |         7 |   93150 | lost   |               36.00 |
 ---- ----------- --------- -------- ---------------------

and update status to win/lose/draw, the highest score is the winner.

Draw if win_rate_percentage have the same score.

CodePudding user response：

Here is query written and tested in workbench. Let's make it more interesting by adding two rows which should be deemed as draw.

create table game ( id int, battle_id int, user_id int, status varchar(10), win_rate_percentage decimal(5,2));
insert game values
(13,    7,  542,    'lost', 18.00),
(14,    7,  93150,  'lost', 36.00),
(15,    8,  542,    'lost', 20.00),
(14,    8,  93150,  'lost', 20.00)
;
update game tb1, (select t1.battle_id, mx,count(1) ct from
                    game t1, (select battle_id,max(win_rate_percentage) mx 
                             from game 
                             group by battle_id) t2
                 where t1.battle_id=t2.battle_id and t1.win_rate_percentage=t2.mx
                 group by t1.battle_id) tb2
set  tb1.status=case 
                when ( tb1.win_rate_percentage=tb2.mx and tb2.ct>1 ) then 'draw' 
                when ( tb1.win_rate_percentage=tb2.mx and tb2.ct=1 ) then 'win'
                else 'lose' end
where tb1.battle_id=tb2.battle_id 
;

select * from game;

-- result set:
# id, battle_id, user_id, status, win_rate_percentage
13, 7, 542, lose, 18.00
14, 7, 93150, win, 36.00
15, 8, 542, draw, 20.00
14, 8, 93150, draw, 20.00