--学生成绩表(姓名,科目,分数)
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;