Java教程

SQL刷题笔记

本文主要是介绍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)
这篇关于SQL刷题笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航