sql查询前五名 |
您所在的位置:网站首页 › sql查询排名前五 › sql查询前五名 |
1.什么是窗口函数
窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。 窗口函数也叫OLAP(Online Analytical Processing)函数,即对数据库数据进行实时分析处理,窗口函数就是为了实现OLAP而添加的标准SQL功能 窗口函数语法: over ([partition by ]) order by )窗口函数可以放以下两种函数: 1.rank , dense_rank , row_number等专用窗口函数 2.能够作为窗口函数的聚合函数(sum , avg , count , max , min等) 由于窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select字句中 2.如何使用窗口函数![]() 在每个班级内按成绩排名: select*, rank() over (partition by 班级 order by 成绩 desc) as ranking from 班级表; select*, rank() over (order by 成绩 desc) as ranking from 班级表;partition by:功能类似于group by,对表进行分组即按班级来进行分组,若省略partition by子句,则将会对所有成绩排序 order by:对分组后的结果进行排序,desc表示降序排序 结果如下 ![]() ![]() 窗口函数group by子句分组和order by字句排序的功能,但是,使用group by分组汇总后改变了表的行数,相当于根据分组的类别将同类的行合并了。同样使用班级表为初始表,具体代码及结果如下: select 班级,count(学号) from 班级表 group by 班级; select 班级, count(学号) over (partition by 班级 order by 班级) as current_count from 班级表;![]() ![]() 总的来说,窗口函数在具有分组及排序的功能的同时还不会减少原表的行数。 3.其他专用窗口函数窗口函数中关于排序三个窗口函数为rank , dense_rank , row_number,这三个专用窗口函数的区别如下: select*,rank() over (order by 成绩 desc) as ranking from 班级表; select*,dense_rank() over (order by 成绩 desc) as ranking from 班级表; select*,row_number() over (order by 成绩 desc) as ranking from 班级表;![]() ![]() ![]() rank函数:若排名时有并列名次的行,会占用下一名次的位置,即如结果1所示,会有3个第五名但第五名之后就是第八名了。 dense_rank函数:若有并列名次的行,不占用下一名次的位置,即如结果2所示,即在3个第五名之后是第六名。 row_number函数:不考虑并列名次的情况,排名会对三个相同的成绩根据其他列的情况重新排名。 最后,在上述这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以了。 面试经典topN问题: ![]() 按课程号分组取成绩最大值所在行的数据 select* from score as a where 成绩 = (select max(成绩) from score as b where a.课程号= b.课程号 group by 课程号);![]() 使用关联子查询可以达到目的,注意,子查询中必须添加where a.课程号 = b.课程号 的条件,否则,子查询返回多行的数据,会报错。程序无法运行。 -- 错误示范 select* from score where 成绩 = (select max(成绩) from score group by 课程号);![]() 按课程号分组取成绩最小值所在行的数据 select* from score as a where 成绩 = (select min(成绩) from score as b where a.课程号 = b.课程号 group by 课程号);![]() 同样适用关联子查询可以达到目的。 查询每名学生成绩前两名的纪录 方法一:先使用group by 将课程分组,之后再通过order by对成绩排序,并用limit限制输出的行数,将每个学生最高的两门课的成绩取出来 (select* from score where 学号 = '0001' order by 成绩 desc limit 2) union (select* from score where 学号 = '0002' order by 成绩 desc limit 2) union (select* from score where 学号 = '0003' order by 成绩 desc limit 2);![]() 方法二:使用窗口函数 select*,row_number() over (partition by 学号 order by 成绩 desc) as ranking from score;![]() 为得到每个学生成绩最高的两门课的成绩,需要加ranking |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |