OVER 轻松搞定排名查询

最新推荐文章于 2022-08-11 15:43:55 发布

千里--伸

最新推荐文章于 2022-08-11 15:43:55 发布

阅读量720

点赞数

分类专栏： MS SQL 文章标签： OVER sql SQL 排名

本文链接：https://blog.csdn.net/akuoma/article/details/8260326

版权

MS SQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

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

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 年级='一年级'