日期:2014-05-17 浏览次数:20949 次
with tb_test as(
select 2 "年级" ,1 "班级" ,'英语' "科目" ,'王五' "姓名" ,88 "分数" from dual union
select 2 ,1 ,'数学' ,'麻六' ,79 from dual union
select 2 ,2 ,'英语' ,'小七' ,69 from dual union
select 3 ,1 ,'英文' ,'蕫永' ,92 from dual union
select 3 ,4 ,'语文' ,'牛群' ,84 from dual union
select 3 ,4 ,'语文' ,'蕫永' ,91 from dual
)
select "年级","班级","科目","姓名","分数"
from
(
select t.*,rank() over(partition by "年级" ,"班级" ,"科目" order by "分数" desc ) rnk
from tb_test t
) t_1
where rnk = 1