178.Rank_Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie,the next ranking number should be the next consecutive integer value.

#+----+-------+
#| 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    |
#+-------+------+

sql脚本

SELECT
  Score,
  @rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
  Scores,
  (SELECT @rank := 0, @prev := -1) init
ORDER BY Score DESC