Create table
create table TEST_RANK_OVER
(
CLASS NUMBER()
NAME VARCHAR()
SCORE NUMBER() default
);
delete from test_rank_over;
insert into test_rank_over (class namescore)values( a );
insert into test_rank_over (class namescore)values( b );
insert into test_rank_over (class namescore)values( c );
insert into test_rank_over (class namescore)values( d );
insert into test_rank_over (class namescore)values( a );
insert into test_rank_over (class namescore)values( b );
insert into test_rank_over (class namescore)values( c );
insert into test_rank_over (class namescore)values( d );
insert into test_rank_over (class namescore)values( a );
insert into test_rank_over (class namescore)values( b );
insert into test_rank_over (class namescore)values( c );
insert into test_rank_over (class namescore)values( d );
insert into test_rank_over (class namescore)values( e );
跳跃排名语法
select class name score rank() over(partition by class order by score desc) rank
from test_rank_over;
查询结果
class name score rank
d
a （与下一个排名相同都是第二名）
b （与上一个排名相同都是第二名）
c （跳过第三名）
d
a
c
b
d
e
a
c
b
说明rank() over(partition by class order by score desc)先对班级分组然后根据成绩进行排名如果分数相同则排名相同接下来的排名会跳跃N个（N为排名相同的人数）
非跳跃排名语法
select class name score dense_rank() over(partition by class order by score desc)rank from test_rank_over;
查询结果
class name score rank
d
a
b
c
d
a
c
b
d
e
a
c
b
