＋设为首页 ＋添加到收藏夹
一个学生总表，能否一个查询，得到所有学生在本班机的成绩排名
假设成绩字段 score,班机字段 class_code,学生号 stu_id
可以允许一个辅助表，谢谢高手指点
select rank() over(order by score) as 排名,t.* from t;
select class_code, stu_id, score, id from (
select t.*, rank() over(partition by class_code order by score) id
from table_name t )
order by class_code, stu_id
用 rank() 成绩一样有跳跃 如:
90 1
90 1
80 3
select class_code, stu_id, score, id from (
select t.*, dense_rank() over(partition by class_code order by score) id
from table_name t )
用 dense_rank()没有跳跃 如：
90 1
90 1
80 2
select class_code, stu_id, score, id from (
select t.*, row_number() over(partition by class_code order by score) id
from table_name t )
用 row_number() 成绩一样不做并列处理
90 1
90 2
80 3
班级排名应该分组的
select rank() over(partition by class_code order by score desc) as 排名,t.* from t;
