不多说，上代码，可以根据需要，分年级，科目，及性别出排名

drop table #t1;
create table #t1 select年级 varchar select50,科目 varchar select50,学生名 varchar select20,性别 varchar select2,分数 decimal select18,2

insert into #t1 select '一年级','语文','akuoma1','男',90.5
insert into #t1 select '一年级','语文','akuoma2','男',90.5
insert into #t1 select '一年级','语文','akuoma3','男',91.5
insert into #t1 select '一年级','数学','akuoma1','男',90.5
insert into #t1 select '一年级','数学','akuoma2','男',92.5
insert into #t1 select '一年级','数学','akuoma3','男',93.5
insert into #t1 select '一年级','美术','akuoma1','男',91.5
insert into #t1 select '一年级','美术','akuoma2','男',90.5
insert into #t1 select '一年级','美术','akuoma3','男',90.5
insert into #t1 select '一年级','英语','akuoma1','男',90.5
insert into #t1 select '一年级','英语','akuoma2','男',92.5
insert into #t1 select '一年级','英语','akuoma3','男',94.5

insert into #t1 select '一年级','语文','lisa1','女',91.5
insert into #t1 select '一年级','语文','lisa2','女',92.5
insert into #t1 select '一年级','语文','lisa3','女',91.5
insert into #t1 select '一年级','数学','lisa1','女',93.5
insert into #t1 select '一年级','数学','lisa2','女',94.5
insert into #t1 select '一年级','数学','lisa3','女',95.5
insert into #t1 select '一年级','美术','lisa1','女',95.5
insert into #t1 select '一年级','美术','lisa2','女',96.5
insert into #t1 select '一年级','美术','lisa3','女',97.5
insert into #t1 select '一年级','英语','lisa1','女',99.5
insert into #t1 select '一年级','英语','lisa2','女',98.5
insert into #t1 select '一年级','英语','lisa3','女',93.5

insert into #t1 select '二年级','语文','akuoma1','男',90.5
insert into #t1 select '二年级','语文','akuoma2','男',90.5
insert into #t1 select '二年级','语文','akuoma3','男',91.5
insert into #t1 select '二年级','数学','akuoma1','男',90.5
insert into #t1 select '二年级','数学','akuoma2','男',92.5
insert into #t1 select '二年级','数学','akuoma3','男',93.5
insert into #t1 select '二年级','美术','akuoma1','男',91.5
insert into #t1 select '二年级','美术','akuoma2','男',90.5
insert into #t1 select '二年级','美术','akuoma3','男',90.5
insert into #t1 select '二年级','英语','akuoma1','男',90.5
insert into #t1 select '二年级','英语','akuoma2','男',92.5
insert into #t1 select '二年级','英语','akuoma3','男',94.5

insert into #t1 select '二年级','语文','lisa1','女',91.5
insert into #t1 select '二年级','语文','lisa2','女',92.5
insert into #t1 select '二年级','语文','lisa3','女',91.5
insert into #t1 select '二年级','数学','lisa1','女',93.5
insert into #t1 select '二年级','数学','lisa2','女',94.5
insert into #t1 select '二年级','数学','lisa3','女',95.5
insert into #t1 select '二年级','美术','lisa1','女',95.5
insert into #t1 select '二年级','美术','lisa2','女',96.5
insert into #t1 select '二年级','美术','lisa3','女',97.5
insert into #t1 select '二年级','英语','lisa1','女',99.5
insert into #t1 select '二年级','英语','lisa2','女',98.5
insert into #t1 select '二年级','英语','lisa3','女',93.5

select DENSE_RANK() over( partition by 年级,科目 order by 年级,科目,分数 Desc) as 排名,t.*
from #t1 t where 年级='一年级'