row_number()分组排序

--学生成绩表(姓名，科目，分数）
create table studentscores
(
id uniqueidentifier not null primary key default newid()
,name nvarchar(36) 
,subject nvarchar(36)
,scores decimal(14,1)
)
insert into studentscores(name,subject,scores) values('张三','语文',59)
insert into studentscores(name,subject,scores) values('张三','数学',80)
insert into studentscores(name,subject,scores) values('张三','英语',100)
insert into studentscores(name,subject,scores) values('张三','语文',65)--补考

insert into studentscores(name,subject,scores) values('李四','语文',76)
insert into studentscores(name,subject,scores) values('李四','数学',56)
insert into studentscores(name,subject,scores) values('李四','英语',80)
insert into studentscores(name,subject,scores) values('李四','数学',60)--补考

insert into studentscores(name,subject,scores) values('王五','语文',96)
insert into studentscores(name,subject,scores) values('王五','数学',85)
insert into studentscores(name,subject,scores) values('王五','英语',64)

--取每个人各门功课最好成绩，row_number()按name,subject分组，scores由高到低
select id,name,subject,scores from(
select 
id,name,subject,scores
,row_number() over(partition by name,subject order by scores desc) num
from studentscores
)t
where num=1

--取各科目成绩排名前2的记录

select id,name,subject,scores from(
select 
id,name,subject,scores
,row_number() over(partition by subject order by scores desc) num
from studentscores
)t
where num<=2
order by subject,scores desc

--保留每个人各科目最高分的记录，删除其它记录

delete studentscores where id 
in(
    select id from(
        select id
        ,row_number() over(partition by name,subject order by scores desc) num
        from studentscores)t1
     where num<>1
)

drop table studentscores;