@
目录
注意:排名函数可以跟Over(),但是不能定义window_clause。在计算名次前,需要先排序!
select *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score
表及字段:score.name | score.subject | score.score
select *,rank() over(partition by subject order by score desc)
from score
2. 给每个学生的总分进行排名
select name,sumscore,rank() over( order by sumscore desc)
from
(select name,sum(score) sumscore
from score
group by name) tmp
3. 求每个学生的成绩明细及给每个学生的总分和总分排名
select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name) sumscore
from score) tmp
4. 只查询每个科目的成绩的前2名
select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2
5. 查询学生成绩明细,并显示当前科目最高分
select *,max(score) over(partition by subject)
from score
# 或
select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score