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 3select 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 2select 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;
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 3select 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 2select 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;