MS SQL Server partition by 函数实战三 成绩排名
目录
需求
范例运行环境
视图样本设计
功能实现
基础数据展示
SQL语句
继续排序
小结
需求
假设有若干已更新考试成绩的考生，考试成绩包括总成绩、分项成绩1、分项成绩2，其它信息包括应聘岗位名称、姓名等信息。现希望根据总成绩计算排名，成绩越高排名越靠前，相同成绩排名并列，另外有并列则按总数递增，如两个第1后是第3。本文将继续介绍利用partition by 来实现这一需求，主要实现如下功能：
（1）编写视图获取基础数据，包括人员基本信息和考试成绩数据等
（2）通过 partition by 计算排名，得到新的视图
（3）根据其它要求计算新的排名
范例运行环境
操作系统： Windows Server 2019 DataCenter
数据库：Microsoft SQL Server 2016
.netFramework 4.7.2
视图样本设计
视图成绩表 [v_cj] 设计如下：
序号
字段名
类型
说明
备注
1
zwmc
nvarchar
职位名称
应聘的岗位名称，以此进行分区排序
2
xm
nvarchar
姓名
3
kscj1
float
考试成绩1
计算总成绩的一个分项
4
kscj2
float
考试成绩2
计算总成绩的一个分项
5
kszcj
float
考试总成绩
计算排名的依据
功能实现
基础数据展示
选择数据视图 v_cj，在查询分析器示例如下图：
如图这是随时显示的基础数据（未排序）。
SQL语句
排序的SQL语句，代码如下：
select zwmc,xm,kscj1,kscj2,kszcj ,rank() over (partition by zwmc order by kszcj desc) as ranktip from V_cj运行查询分析器，显示结果如下：
如图框定部分，按最高成绩分值进行倒排序，分值越高排名越高。当成绩相同时则并列排序，下一排序号根据总排序号递增加1，如有两个第6名，则下个排名从第8名开始，以此类推。
关键语句：rank() over (partition by zwmc order by kszcj desc)，按 zwmc （职位名称）分区，以kszcj(考试总成绩) 降序排列，rank() 函数起到了排序功能。
继续排序
如果排序相同，则按分项成绩1继续排序，如果仍然一样则按分项成绩2继续排序，均为降序，SQL 语句如下：
select zwmc,xm,kscj1,kscj2,kszcj,ranktip from V_cj order by zwmc,ranktip,kscj1 desc,kscj2 desc代码继续将 ranktip 字段封装到 v_cj 视图中，然后按照设计的顺序进行排序，如下图运行分析结果查询：
小结
我们可以通过 row_number函数计算总排序（当排名相同时仍然以分项成绩1和分项成绩2降序计算排名，分值越高排名越靠前），可通过如下语句实现：
select zwmc,xm,kscj1,kscj2,kszcj,ranktip, row_number() over (partition by zwmc order by kszcj desc,kscj1 desc,kscj2 desc) as ranktip2 from V_cj关键语句：row_number() over (partition by zwmc order by kszcj desc,kscj1 desc,kscj2 desc)，按 zwmc （职位名称）分区，以kszcj(考试总成绩) 降序排列，第二排序以kscj1(分项考试成绩1) 降序排列，，第三排序以kscj2(分项考试成绩2) 降序排列，row_number() 函数起到了总排序功能。
运行查询分析器，结果显示如下：
如图 ranktip2 字段显示了总排序，ranktip 字段显示了并列排序的情况，分项成绩参与了最终排序规则的实现。
更多partition by 的聚合统计方法可参考我的文章《MS SQL Server partition by 函数实战 统计与输出》
至此 partition by 的实例应用我们就介绍到这里，具体使用中我们还需要灵活掌握。感谢您的阅读，希望本文能够对您有所帮助。