mysql排序-函数dense_rank的应用

给出stuinfo（学生信息表）、classinfo（班级信息表）、exams（考试成绩表）,统计（笔试成绩）成绩排名前3的学生！

通过查询，获取各个班级（笔试成绩）成绩排名前3的学生，需要展示学号、姓名、年龄、班级序号、班级名称、笔试成绩等信息，叠加、依次排序；

我们可以一步一步的实现：

1.学生取得班级信息

SELECT s1.stuno,s1.stuname,s1.stuage,c1.id,c1.name FROM (stuinfo s1 JOIN classinfo c1 ON s1.classid=c1.id);

2.获取有成绩的学生相应信息

SELECT d1.stuno,d1.stuname,d1.stuage,d1.id,d1.name,e1.wscores FROM (exams e1 JOIN (SELECT s1.stuno,s1.stuname,s1.stuage,c1.id,c1.name FROM (stuinfo s1 JOIN classinfo c1 ON s1.classid=c1.id)) d1 ON e1.stuno=d1.stuno);

3.如何取得排名，就是关键了

对于“叠加、依次排序”，我们需要使用到dense_rank()排名函数

先进行排名，获得所有排名情况

SELECT d1.stuno,d1.stuname,d1.stuage,d1.id,d1.name,e1.wscores,(dense_rank() OVER(PARTITION BY d1.id ORDER BY e1.wscores DESC)) AS ranking FROM (exams e1 JOIN (SELECT s1.stuno,s1.stuname,s1.stuage,c1.id,c1.name FROM (stuinfo s1 JOIN classinfo c1 ON s1.classid=c1.id)) d1 ON e1.stuno=d1.stuno);

此时，通过“ranking”列的排名，可以清楚的知道各个班级的排名总情况！

4.接下来，需要隐藏“ranking”列，同时展现前3排名

SELECT d2.stuno,d2.stuname,d2.stuage,d2.id AS 'classid',d2.name AS 'classname',d2.wscores FROM (SELECT d1.stuno,d1.stuname,d1.stuage,d1.id,d1.name,e1.wscores,(dense_rank() OVER(PARTITION BY d1.id ORDER BY e1.wscores DESC)) AS ranking FROM (exams e1 JOIN (SELECT s1.stuno,s1.stuname,s1.stuage,c1.id,c1.name FROM (stuinfo s1 JOIN classinfo c1 ON s1.classid=c1.id)) d1 ON e1.stuno=d1.stuno)) d2 WHERE d2.ranking<=3;

如果需要得到前2的排名，只需要将“d2.ranking<=3”中3改为2即可，如下：

SELECT d2.stuno,d2.stuname,d2.stuage,d2.id AS 'classid',d2.name AS 'classname',d2.wscores FROM (SELECT d1.stuno,d1.stuname,d1.stuage,d1.id,d1.name,e1.wscores,(dense_rank() OVER(PARTITION BY d1.id ORDER BY e1.wscores DESC)) AS ranking FROM (exams e1 JOIN (SELECT s1.stuno,s1.stuname,s1.stuage,c1.id,c1.name FROM (stuinfo s1 JOIN classinfo c1 ON s1.classid=c1.id)) d1 ON e1.stuno=d1.stuno)) d2 WHERE d2.ranking<=2;

具体其他的排名方式统计，后续...