转载:SQL中row_number() over(partition by)的用法说明_Mysql_脚本之家 (jb51.net)
select * from { select cj.xh,--学生学号 cj.cj,--学生成绩 cj.ks_sj,--考试时间 row_number() over(partition by cj.xh order by cj.ks_sj desc) num from ks_cj cj --考试成绩表 } where num = '1'
这篇文章主要介绍了SQL中row_number() over(partition by)的用法说明,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教
目录
row_number 语法
ROW_NUMBER()函数将针对SELECT语句返回的每一行,从1开始编号,赋予其连续的编号。在查询时应用了一个排序标准后,只有通过编号才能够保证其顺序是一致的,当使用ROW_NUMBER函数时,也需要专门一列用于预先排序以便于进行编号
partition by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,分区函数一般与排名函数一起使用。
原始表score
s_id 表是学生编号,c_id表是课程编号,s_score 表是学生对应的课程分数
1.要求:得出每门课程的学生成绩排序(升序)
----因为是每门课程的结果,并且要排序,所以用row_number
1 |
select * ,row_number() over (partition by c_id order by s_score) from score;
|
返回结果:
2:进一步要求:得出每门课程的学生成绩,并且按照70分作为分割线排序—即低于70分的排序,高于70分的排序
1 |
select * ,row_number() over (partition by c_id,( case when s_score>70 then 1 else 0 end ) order by s_score) from score;
|
返回结果:
row_number() over(partition by 列名1 order by 列名2 desc)的使用
表示根据 列名1 分组,然后在分组内部根据 列名2 排序,而此函数计算的值就表示每组内部排序后的顺序编号,可以用于去重复值
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
---查询所有姓名,如果同名,则按年龄降序
1 |
SELECT name ,age,detail,ROW_NUMBER() OVER(PARTITION BY name ORDER BY age DESC ) FROM TEST_Y;
|
通过上面的语句可知,是按照name字段分组,按age字段排序的。
如果只需查询出不重复的姓名即可,则可使用如下的语句, 由查询结果可知,姓名相同年龄小的数据被过滤掉了;
1 |
SELECT * FROM ( SELECT name ,age, detail ,ROW_NUMBER() OVER ( PARTITION BY name ORDER BY age DESC )RN FROM TEST_Y ) WHERE RN= 1 ;
|
分页
--先做一个子查询,先按id1进行排序,排序完后,给每条记录进行了编号
--然后再将子查询做为一张表,就可以进行分页了
1 2 3 |
select *
from ( select t.*,row_number() over( order by t.id1 asc ) as rn from demo t) d
where d.rn between 1 and 2
|