排名函数
排名函数也需要和over搭配使用, 是窗口函数。
1)rank:排名相同时会重复,总数不变;
2)dense_rank:排名相同时会重复, 总数会减少;
3)row_number:会根据顺序计算;
number rank dense_rank row_number
100 1 1 1
100 1 1 2
99 3 2 3
98 4 3 4
需求案例
select name,
subject,
score,
rank() over(partition by subject order by score) rank1,
dense_rank over(partition by subject order by score) rank2,
row_number over(partition by subject order by score) rank3
from table_name
结果如下: