178. Rank Scores

难度中等642
SQL架构
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. In other words, there should be 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 |
+———-+————-+

Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example Rank.

  1. SELECT
  2.         score as Score,
  3.     CAST(    @rank_no :=
  4.     IF
  5.         ( @pre <>( @pre := score ), @rank_no + 1, @rank_no ) as SIGNED) AS `Rank` 
  6.     FROM
  7.         scores,(
  8.         SELECT
  9.             @pre :=- 1,
  10.             @rank_no := 0 
  11.         ) init 
  12.     ORDER BY
  13.     score DESC

这里用到了SELECT
@pre :=- 1,
@rank_no := 0
) init 来初始化变量，以及 @pre <>( @pre := score )来对@pre赋值，以及( @rank_no :=
IF
( @pre <>( @pre := score ), @rank_no + 1, @rank_no ) as SIGNED)对@rank_no赋值