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] 设计如下：
序号字段名类型说明备注1zwmcnvarchar职位名称应聘的岗位名称，以此进行分区排序2xmnvarchar姓名3kscj1float考试成绩1盘算总成绩的一个分项4kscj2float考试成绩2盘算总成绩的一个分项5kszcjfloat考试总成绩盘算排名的依据
功能实现


基础数据展示

选择数据视图 v_cj，在查询分析器示例如下图：

如图这是随时表现的基础数据（未排序）。
SQL语句

排序的SQL语句，代码如下：

1. select  zwmc,xm,kscj1,kscj2,kszcj
2. ,rank() over (partition by  zwmc order by kszcj desc) as ranktip
4. from V_cj

复制代码

运行查询分析器，表现结果如下：

如图框定部分，按最高成绩分值进行倒排序，分值越高排名越高。当成绩雷同时则并列排序，下一排序号根据总排序号递增长1，如有两个第6名，则下个排名从第8名开始，以此类推。
关键语句：rank() over (partition by  zwmc order by kszcj desc)，按 zwmc （职位名称）分区，以kszcj(考试总成绩) 降序分列，rank() 函数起到了排序功能。
继续排序

如果排序雷同，则按分项成绩1继续排序，如果仍然一样则按分项成绩2继续排序，均为降序，SQL 语句如下：

1. select  zwmc,xm,kscj1,kscj2,kszcj,ranktip
2.    from V_cj order by zwmc,ranktip,kscj1 desc,kscj2 desc

复制代码

代码继续将 ranktip 字段封装到 v_cj 视图中，然后按照设计的顺序进行排序，如下图运行分析结果查询：

小结

我们可以通过 row_number函数盘算总排序（当排名雷同时仍然以分项成绩1和分项成绩2降序盘算排名，分值越高排名越靠前），可通过如下语句实现：

1. select  zwmc,xm,kscj1,kscj2,kszcj,ranktip,
2. row_number() over (partition by  zwmc order by kszcj desc,kscj1 desc,kscj2 desc) as ranktip2
3.    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 的实例应用我们就介绍到这里，具体使用中我们还必要灵活掌握。感谢您的阅读，盼望本文能够对您有所资助。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。