原始数据:
name class s
a1 2 74
a2 1 95
a3 1 95
a4 1 80
a5 2 92
a6 3 99
a7 3 99
a8 3 45
a9 3 55
a10 3 78
rank() over()
select * from
(
select name,class,s,rank() over(partition by class order by s desc) num from t1
)
where num=1;
a2 1 95 1
a3 1 95 1
a5 2 92 1
a6 3 99 1
a7 3 99 1
row_number() over()
select * from
(
select name,class,s,row_number() over(partition by class order by s desc) num from t2
)
where num=1;
class s mm
1 95 1 --95有两名但是只显示一个
2 92 1
3 99 1 --99有两名但也只显示一个