[SQL] MySQL实现多种排名查询

示例：

Scores 表如下：

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

SQL架构：点击此处

---

一、普通排名（排名无并列）

按成绩排序后的顺序，排名逐一增1，对相同的成绩按 Id 递增的顺序递增排名。（无相同排名的情况）

1. 升序查询

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

SQL语句：

SELECT Id, Score, @rank := @rank + 1 AS 'Rank'
FROM Scores, (SELECT @rank := 0) r
ORDER BY Score;

2. 降序查询

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