OVER 轻松搞定排名查询

来源：互联网发布：ipad 软件开发编辑：程序博客网时间：2024/06/14 05:06
不多说，上代码，可以根据需要，分年级，科目，及性别出排名
drop table #t1;create table #t1 select年级 varchar select50,科目 varchar select50,学生名 varchar select20,性别 varchar select2,分数 decimal select18,2insert into #t1 select '一年级','语文','akuoma1','男',90.5insert into #t1 select '一年级','语文','akuoma2','男',90.5insert into #t1 select '一年级','语文','akuoma3','男',91.5insert into #t1 select '一年级','数学','akuoma1','男',90.5insert into #t1 select '一年级','数学','akuoma2','男',92.5insert into #t1 select '一年级','数学','akuoma3','男',93.5insert into #t1 select '一年级','美术','akuoma1','男',91.5insert into #t1 select '一年级','美术','akuoma2','男',90.5insert into #t1 select '一年级','美术','akuoma3','男',90.5insert into #t1 select '一年级','英语','akuoma1','男',90.5insert into #t1 select '一年级','英语','akuoma2','男',92.5insert into #t1 select '一年级','英语','akuoma3','男',94.5insert into #t1 select '一年级','语文','lisa1','女',91.5insert into #t1 select '一年级','语文','lisa2','女',92.5insert into #t1 select '一年级','语文','lisa3','女',91.5insert into #t1 select '一年级','数学','lisa1','女',93.5insert into #t1 select '一年级','数学','lisa2','女',94.5insert into #t1 select '一年级','数学','lisa3','女',95.5insert into #t1 select '一年级','美术','lisa1','女',95.5insert into #t1 select '一年级','美术','lisa2','女',96.5insert into #t1 select '一年级','美术','lisa3','女',97.5insert into #t1 select '一年级','英语','lisa1','女',99.5insert into #t1 select '一年级','英语','lisa2','女',98.5insert into #t1 select '一年级','英语','lisa3','女',93.5insert into #t1 select '二年级','语文','akuoma1','男',90.5insert into #t1 select '二年级','语文','akuoma2','男',90.5insert into #t1 select '二年级','语文','akuoma3','男',91.5insert into #t1 select '二年级','数学','akuoma1','男',90.5insert into #t1 select '二年级','数学','akuoma2','男',92.5insert into #t1 select '二年级','数学','akuoma3','男',93.5insert into #t1 select '二年级','美术','akuoma1','男',91.5insert into #t1 select '二年级','美术','akuoma2','男',90.5insert into #t1 select '二年级','美术','akuoma3','男',90.5insert into #t1 select '二年级','英语','akuoma1','男',90.5insert into #t1 select '二年级','英语','akuoma2','男',92.5insert into #t1 select '二年级','英语','akuoma3','男',94.5insert into #t1 select '二年级','语文','lisa1','女',91.5insert into #t1 select '二年级','语文','lisa2','女',92.5insert into #t1 select '二年级','语文','lisa3','女',91.5insert into #t1 select '二年级','数学','lisa1','女',93.5insert into #t1 select '二年级','数学','lisa2','女',94.5insert into #t1 select '二年级','数学','lisa3','女',95.5insert into #t1 select '二年级','美术','lisa1','女',95.5insert into #t1 select '二年级','美术','lisa2','女',96.5insert into #t1 select '二年级','美术','lisa3','女',97.5insert into #t1 select '二年级','英语','lisa1','女',99.5insert into #t1 select '二年级','英语','lisa2','女',98.5insert into #t1 select '二年级','英语','lisa3','女',93.5select DENSE_RANK() over( partition by 年级,科目 order by 年级,科目,分数 Desc) as 排名,t.*from #t1 t where 年级='一年级'