SQL刷题笔记

在重复数据的前提下进行数据排名

mysql> SELECT * FROM grade;
+----+-------+
| id | score |
+----+-------+
|  1 |    60 |
|  2 |    60 |
|  3 |    90 |
|  4 |    80 |
|  5 |    80 |
|  6 |    40 |
|  7 |    90 |
|  8 |    30 |
|  9 |    80 |
| 10 |    70 |
+----+-------+
10 rows in set (0.00 sec)
-- 给分数进行排名，如果两条数据的分数相等则按照id进行升序排名
mysql> SELECT a.id AS id, a.score AS score, (select COUNT(DISTINCT b.score) FROM grade AS b where b.score >= a.score)
    -> AS rank
    -> FROM grade AS a
    -> ORDER BY rank ASC, id ASC;
+----+-------+------+
| id | score | rank |
+----+-------+------+
|  3 |    90 |    1 |
|  7 |    90 |    1 |
|  4 |    80 |    2 |
|  5 |    80 |    2 |
|  9 |    80 |    2 |
| 10 |    70 |    3 |
|  1 |    60 |    4 |
|  2 |    60 |    4 |
|  6 |    40 |    5 |
|  8 |    30 |    6 |
+----+-------+------+
10 rows in set (0.00 sec)