Geek Answers Handbook

MySQL Winning Streak for each player

I have a table with a win and losing statistics from the game:

id winner_id loser_id 1 1 2 2 1 2 3 3 4 4 4 3 5 1 2 6 2 1 7 3 4 8 3 2 9 3 5 10 3 6 11 2 3 12 3 6 13 2 3

I need a scoreboard where I can find the highest winning streak of each player in the game. The player’s lane is broken when he lost the game (player_id = loser_id). It should look like this:

 player_id win_streak 1 3 2 2 3 4 4 1 5 0 6 0

I have tried many queries with user defined variables, etc., but I can not find a solution. Thanks!

SQL Fiddle: http://sqlfiddle.com/#!9/3da5f/1

+6
source share
2 answers

I think you better do this on the php side (or in any other language).

But just to give you some idea and as an experiment and example for some unique cases (I hope this can be useful somewhere)

Here is my approach:

http://sqlfiddle.com/#!9/57cc65/1

 SELECT r.winner_id, (SELECT MAX(IF(winner_id=r.winner_id,IF(@i IS NULL, @i:=1,@i: =@i +1), IF(loser_id = r.winner_id, @i:=0,0))) FROM Results r1 WHERE r1.winner_id = r.winner_id OR r1.loser_id = r.winner_id GROUP BY IF(winner_id=r.winner_id, winner_id,loser_id)) win_streak FROM ( SELECT winner_id FROM Results GROUP BY winner_id ) r

Now it returns not all identifiers, but only the one who has ever won. Therefore, to make it better, perhaps you have a user table. If so, this will simplify the request. If you do not have a user table, you need union all somehow users who have never won.

Welcome if you have any questions.

+2
source

This is the same as the Alex approach. I'm not quite sure, except that he has one clear advantage ....; -)

 SELECT player_id, MAX(CASE WHEN result = 'winner' THEN running ELSE 0 END) streak FROM ( SELECT * , IF(player_id = @prev_player,IF( result=@prev _result,@i: =@i +1,@i:=1),@i:=1) running , @prev_result := result , @prev_player:=player_id FROM ( SELECT id, 'winner' result, winner_id player_id FROM my_table UNION SELECT id, 'loser', loser_id FROM my_table ) x , ( SELECT @i:=1,@prev_result = '',@prev_player:='' ) vars ORDER BY x.player_id , x.id ) a GROUP BY player_id;
+3
source

More articles:


All Articles