How to use the variable in MySQL skillfully

Write a SQL query to rank scores. If there is a tie between the scores, both should has the same ranking. Note that after a tie, the next ranking number should is the next consecutive integer value. In the other words, there should is no "holes" between ranks.

+----+-------+| Id | Score |+----+-------+| 1  | 3.50  | | 2  | 3.65  | | 3  | 4.00  | | 4  | 3.85  | | 5  | 4.00  | | 6  | 3.65  |+ ----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

 +-------+------+| Score | Rank |+-------+------+| 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | |  3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 |+-------+------+ 
 
 This problem lets us add weights to score, with the same values having the same weight, and the weights being consecutive numbers. 
 
 It's easy to solve if you're programming, but it's not always easy to put it in a SQL statement. From the thinking, this solution is very simple, as long as the first order of score, if the current value of score than the previous score value, the weight of the self-increment 1. Maintain the original value if it is not greater than the previous score 
 
 If the following conditions are reduced, our job is actually to give score a sequence number. Then our operation becomes simple. 

Select (@i:=@i+1 as I,table_name. *  from TABLE_NAME, (select@i:=0 as it

The above is a random from the Internet to find an automatic add Serial number statement. We look at the SQL statement above and parse the structure of the SQL statement that contains the variable.

First the disturbance item as is removed, as just an alias for the query results, here we do not have to consider as.

Select Score, (@i:=@i+1) from Scores, (Select  @i:=0orderbydesc

The output of this result is:

{"Headers": ["Score", "(@i:[email protected]+1)"], "values": [[4.00, 1.0], [4.00, 2.0], [3.85, 3.0], [3.65, 4.0], [3.65, 5 .0], [3.50, 6.0]]}

Looking at the results above, we have achieved half of the requirements. Just now the sequence number in the result does not meet the "same value has the same weight" requirement. At this point we need to introduce another variable @pre to save the previous score.

If the @pre is equal to the current score when calculating the weight, the @i remains the original value (or +0), otherwise it adds 1.

SelectScore,@i:= @i +(@pre <>(@pre:=score)) Rank fromScores, (Select @i:= 0,@pre:= -1) InitOrder  byScoredesc

It is important to note that the variable @pre. Since we judge in the weights whether the @pre (the previous score value) equals the current score value, if the initial value of the @pre is greater than 0, it is possible to determine an error. It is best to set the initial value of @pre to a number less than 0.

How to use the variable in MySQL skillfully