MS SQL Server partition by 函数实战 统计与输出

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

需求

假设有一课程项目,我们需要统计该项目中的课件数量,并提取课程信息,如课程标题名称、排序号等,如果使用 GROUP BY 聚合函数,则只能统计返回课件项目及对应的课件数量一条记录,无法显示明细信息,对于终端想要进行输出的话,此时 partition by 就派上用场了。


输出如下图:

在管理心理学项目里包括若干课程,我们将根据排序号进行分类输出,显示课程的明细内容并继续其它业务操作。


范例运行环境

操作系统: Windows Server 2019 DataCenter


数据库:Microsoft SQL Server 2016


表及视图样本设计

主表 [ms_project_ep] 项目课程明细表设计如下:


字段名 类型 说明 备注

1

cid

uniqueidentifier

唯一标识


2

project_cid uniqueidentifier 所属项目ID 对应项目表

3

lession_cid tinyint 所属课程ID 对应课程表

4

sortid int
排序号

总排序号

查询分析器结果数据显示如下图: 如图我们看到查询结果按项目ID进行分区,并统计课程数,


如项目ID:7CF46B88-0B4D-49A0-A0D8-08B9AF064AC5,包含了1个课程;


项目ID:EE8AFC3F-5E82-46FA-B81E-10F7F06F61C8,包含了122个课程;


并且每一个分区提取排序号,从1开始。


SQL 语句如下:

select project_cid,lession_cid,
count(lession_cid) over (partition by project_cid order by project_cid) as lcount,
sortid 
from ms_project_ep 
order by project_cid,sortid

关键说明见下表:

序号 关键语句 说明

1

count(lession_cid) over (partition by project_cid order by project_cid)

as lcount

partition by project_cid order by project_cid,按项目ID分区并排序;

使用count函数统计课程数;

小结

partition by 的聚合统计和使用还有很多种,如下表:

序号

统计项

说明

1

row_number() 记录总排序号

2

rank() 排序,有并列则按总数递增,如两个第1后是第3

3

dense_rank() 排序,有并列则按上一数值递增,如两个第1后是第2

4

count(字段名) 求个数

5

max(字段名) 求最大值

6

min(这段名) 求最小值

7

sum(字段名) 求和

8

avg(字段名)

求平均值

9

first_value(字段名) 求第一个值

10

last_value(字段名)

求最后一个值

11

lag(字段名,[行数]) 取指定列,将分区列的数据后错n行,行数不是必选项,默认为0,即不错行

12

lead(字段名,[行数])
取指定列,将分区列的数据前错n行,行数不是必选项,默认为0,即不错行
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2天前
|
SQL XML 关系型数据库
【less-5】基于SQLI的SQL盲注常用函数
【less-5】基于SQLI的SQL盲注常用函数
18 2
|
2天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
21小时前
|
SQL 存储 关系型数据库
SQL Date 函数
SQL Date 函数
21 2
|
21小时前
|
SQL Oracle 关系型数据库
SQL NULL 函数
SQL NULL 函数
13 1
|
3天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
40 3
|
2天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
3天前
|
SQL 弹性计算 API
云服务器 ECS产品使用问题之如何通过API调用阿里云服务器上SQL Server数据库中的数据
云服务器ECS(Elastic Compute Service)是各大云服务商阿里云提供的一种基础云计算服务,它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
|
存储 SQL 程序员
【Sql Server】存储过程通过作业定时执行按天统计记录
通过前两篇文章的学习,我们已经对创建表、存储过程、作业等功能点有所了解 本次将结合前面所学习的知识点,创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值
330 0
【Sql Server】存储过程通过作业定时执行按天统计记录
|
11月前
|
存储 SQL 数据库
SQL Server——为什么要使用存储过程?不使用是什么样的?
提高数据库执行速度,可能第一次见到这句话的小伙伴们感觉到非常的匪夷所思叭!怎么就提高了它的执行速度捏,从哪方面可以表现出来呢?既然这里要说到的是为什么要使用存储过程,也就是说它的优点是什么。那我们肯定就要对使用和不使用存储过程两方面来进行对比才能看出它的优点对吧。
|
存储 SQL Go
SQL Server 存储过程
SQL Server 存储过程
130 0