1,row_number() over()【排序,连续】
SELECT score, row_number ( ) over () FROM sc;
按原始表格的成绩顺序进行排序结果
原始表格成绩顺序表格顺序
先将分数从大到小排序,分数相同,排名不同
SELECT score, row_number ( ) over (order by score) FROM sc;结果
按学科分组后对成绩排序
SELECT score,cid,row_number ( ) over (partition by cid order by score) FROM sc;结果
2,rank() over()【排序,不连续】
SELECT score,rank() over(ORDER BY score DESC) FROM sc;
相同成绩,相同序号,后面的名次不连续结果
SELECT score,cid,rank( ) over (partition by cid order by score) FROM sc;
按对相应学科成绩进行排序,相同成绩,相同序号,不连续结果
3,dense_rank() over()【排序,连续】
SELECT score,dense_rank() over(ORDER BY score DESC) FROM sc;
成绩降序排列,成绩相同,名次相同,连续结果
SELECT score,cid,dense_rank( ) over (partition by cid order by score) FROM sc;
学科按名次排序,成绩相同,名次相同,连续结果
4,ntile(n) over()【平分】
SELECT score,ntile(3) over(ORDER BY score ) FROM sc;
成绩升序排列后,平均分为3组,输出他们组别的序号结果
SELECT score,cid,ntile(3) over (partition by cid order by score) FROM sc;
按学科升序排列后,将每个学课的成绩,平分为3组结果