MySQL: Getting a Query out of the While Loop

The following is a simple example of a multi-player game where many games are played over several seasons. The game id, player name, score of each player and the season which it is being played are recorded in a table. Queries below show how one can derive the summary of a player's rank from all games played in a particular season.

SELECT game, score FROM Table WHERE name='name' AND season='season'
 ORDER BY game;

while(Fetching Result)
{
 SELECT COUNT(*) AS 'total',
  SUM(CASE WHEN score>='score' THEN 1 ELSE 0 END) AS 'rank'
  FROM Table W开发者_运维知识库HERE game='game' AND season='season';
}

The output contains game, score, rank and total from all games played by the player in a particular season, ORDER BY game. I am wondering whether there are more efficient ways to do this instead of using the while loop.

---

Sorry, I got confused by a column name when I first posted. You cannot make much of an optimization here in the query itself, because the second time you want to get data about the games, not the player you are trying to rank. You can add them as subqueries/joins to the first, but the second query works with more than one row, so this can be done, but will result in additional rows being returned.

One way to make things work faster when retrieving this data is to add new information in the DB - rank for each player's games and total participants in the game. You can calculate this right after the game finishes, the same way you do now with the second query, and save the results somewhere where you can access them faster without calculating the information each time you need it.