Mysql rank 计算排名：

给出不同用户的分数，并且计算排名：

CREATE TABLE test_rank(id int,score int);

INSERT INTO test_rank VALUES (1,10),(2,20),(7,20),(3,30),(4,40),(5,40),(6,50);

mysql> select * from test_rank;
+------+-------+
| id   | score |
+------+-------+
|    1 |    10 |
|    2 |    20 |
|    7 |    20 |
|    3 |    30 |
|    4 |    40 |
|    5 |    40 |
|    6 |    50 |
+------+-------+
7 rows in set (0.00 sec)

mysql> set @prev_value := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> set @rank_count := 0;
Query OK, 0 rows affected (0.00 sec)

假设比较到第n行，设置一个变量prev_value,用于存放第n-1行score的分数；用于比较第n行和第n-1行的score，pre_value可以理解为临时保存第n-1行的变量：

rank_count用于保存当前的排名：

mysql> set @prev_value := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> set @rank_count := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> id,
    -> score,
    -> CASE
    -> WHEN @prev_value = score THEN    ##相等则prev_value不变（第一次为null，不会相等，所以跳转到下一个when语句）
    -> @rank_count
    -> WHEN @prev_value := score THEN   ##不等，则第n行的score赋值(：=）给prev_value.且rank_count增加1
    -> @rank_count := @rank_count + 1
    -> END AS rank_column          #case 开始end结束
    -> FROM
    -> test_rank
    -> ORDER BY
    -> score DESC;
+------+-------+-------------+
| id   | score | rank_column |
+------+-------+-------------+
|    6 |    50 |           1 |
|    4 |    40 |           2 |
|    5 |    40 |           2 |
|    3 |    30 |           3 |
|    2 |    20 |           4 |
|    7 |    20 |           4 |
|    1 |    10 |           5 |
+------+-------+-------------+
7 rows in set (0.01 sec)