MS SQL Server partition by 函数实战二 编排考场人员

server/2025/2/7 17:50:54/

目录

需求

输出效果

范例运行环境

表及视图样本设计

功能实现

生成考场数据

生成重复的SQL语句

封装为统计视图

编写存储过程实现统计

小结

需求

假设有若干已分配准考证号的考生，准考证号示例（01010001）共计8位，前4位为分类号，后4位为分类下的总排序号。现提供考场分配信息EXCEL文件（包括考场编号 、考场名称、考场容纳人数等），希望根据准考证号升序，将考生分配于对应的考场中，并生成对应的座位号（也即每一个考场的排序号），即分配原则为准考证号越小，考场号和座位号越靠前。本文将继续介绍利用partition by 、c# 来实现这一需求，主要实现如下功能：

（1）上传考场分布信息的EXCEL，导入生成考场信息数据。

（2）使用 C# 生成重复的SQL语句进行 union all 合并，生成考场座位信息。

（3）将准考证号信息左连接考场座位信息，生成考生分配明细表（包装为视图）。

（4）生成每个考场的准考证号以分类号（前4位）分组统计最小号和最大号范围。

输出效果

实现的考生准考证号分配表如下图：

如图，第1考场可容纳30人，座位号分配则从 1 到 30。分类为0101的准考证号在分配不开的情况下继续分配到第2考场，座位号重新进行分配，以此类推。

另外，实现考场准考证号范围分类统计如下图：

如图，准考证号范围按前4位分组统计，输出最小号与最大号范围。

范例运行环境

操作系统： Windows Server 2019 DataCenter

数据库：Microsoft SQL Server 2016

.netFramework 4.7.1

开发工具：VisualStudio 2019 C#

表及视图样本设计

考场表 [dlzp_kc] 设计如下：

序号

字段名

类型

说明

备注

1

xm_cid

uniqueidentifier

所属项目ID

比如某一考试项目

2

kcbh

nvarchar(6)

考场编号

按固定位补位排序

3

kcmc

nvarchar(50)

考场名称

4

kcrs

smallint

考场人数

含准考证号的考生视图 v_ypz 设计如下：

序号

字段名

类型

说明

备注

1

xm_cid

uniqueidentifier

所属项目ID

比如某一考试项目

2

zkzh

char(8)

准考证号

固定8位

根据设计 v_ypz 数据集记录大于等于考场记录数，因此使用左连接以显示考场座位数不足的情况统计。

准考证号考场范围分布情况表dlzp_kc_zkzhs 设计如下：

序号

字段名

类型

说明

备注

1

xm_cid

uniqueidentifier

所属项目ID

比如某一考试项目

2

kcbh

nvarchar(6)

考场编号

3

kcmc

nvarchar(50)

考场名称

冗余字段

4

zkzhs

nvarchar(500)

准考证号范围

转多行为一行数据

功能实现

生成考场数据

根据提供的EXCEL考场文件，导入到考场表（dlzp_kc）中，如何将EXCEL文件导入成数据集，可参考我的文章《C#实现Excel合并单元格数据导入数据集》，成功导入后，在查询分析器示例如下图：

生成重复的SQL语句

结合考场数据集，通过C# 循环遍历，得到我们想要的考场明细（输出容纳人数的记录数）数据集，代码如下：

//TextBox1为放置在 WebForm 上的文本框控件（多行设置）
//dt 为生成的考场数据集数据表（DataTable）TextBox1.Text = "select b.zkzh,a.* from(select ROW_NUMBER() over(order by zkzh) xh,zkzh  from v_ypz where xm_cid='" + _xm_cid + "' and zkzh is not null)  b left join (select ROW_NUMBER() over(order by kcbh) as xh,* from (";string dsql = "
select top {0} '{1}' kcbh,'{2}' kcmc,{3} kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz {4}";
for (int i = 0; i < dt.Rows.Count; i++)
{TextBox1.Text += string.Format(dsql, dt.Rows[i]["kcrs"].ToString(), dt.Rows[i]["kcbh"].ToString(), dt.Rows[i]["kcmc"].ToString(),dt.Rows[i]["kcrs"].ToString(), i == dt.Rows.Count - 1 ? "" : "union all");
}TextBox1.Text += ") a) a on a.xh=b.xh ";

该程序片断可辅助我们生成想要的SQL语句，以避免重复劳动，生成的最终结果如下：

select b.zkzh,a.* from 
(select xm,sfzh,ROW_NUMBER() over(order by zkzh) xh,zkzh from v_ypz where xm_cid='16286689-1097-4b9d-8c2a-06b4588ec289' and zkzh is not null)  b 
left join (
select ROW_NUMBER() over(order by kcbh) as xh,* from (
select top 30 '001' kcbh,'第1考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '002' kcbh,'第2考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh  from v_ypz union all
select top 30 '003' kcbh,'第3考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '004' kcbh,'第4考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '005' kcbh,'第5考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '006' kcbh,'第6考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '007' kcbh,'第7考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz ) a) a on a.xh=b.xh

在主表 v_ypz 按准考证号升序生成总序号（xh） ROW_NUMBER() over(order by zkzh) xh，考场明细视图按考场编号升序生成总序号（xh）ROW_NUMBER() over(order by kcbh) as xh，其中每个考场的则按考场编号生成座位号（zwh）ROW_NUMBER() over(order by kcbh) zwh，最后我们通过左连接即可得到我们想要的分配明细情况结果。

封装为统计视图

将上述分配明细查询进一步进行统计准考证号范围，封装为视图 v_a，SQL 语句如下：

create view [dbo].[v_a] as 
select kcbh,kcmc,lzkzh,zkzh2,zkzh3 from (select *,left(zkzh,4) lzkzh,
min(right(zkzh,4)) over (partition by kcbh,left(zkzh,4) order by kcbh,left(zkzh,4)) zkzh2,
max(right(zkzh,4)) over (partition by kcbh,left(zkzh,4) order by kcbh,left(zkzh,4)) zkzh3 
from ( 
select b.sfzh,b.xm,b.zkzh,a.* from(select xm,sfzh,ROW_NUMBER() over(order by zkzh) xh,zkzh  from v_ypz where xm_cid='16286689-1097-4b9d-8c2a-06b4588ec289' and zkzh is not null)  b left join (
select ROW_NUMBER() over(order by kcbh) as xh,* from (
select top 30 '001' kcbh,'第1考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '002' kcbh,'第2考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '003' kcbh,'第3考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '004' kcbh,'第4考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '005' kcbh,'第5考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '006' kcbh,'第6考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz union all
select top 30 '007' kcbh,'第7考场' kcmc,30 kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz ) a) a on a.xh=b.xh   ) a   ) a  group by kcbh,kcmc,lzkzh,zkzh2,zkzh3
GO

其中我们通过 partition by 函数 按考场编号 及 左截取准考证号前4位（分类号）进行分区计算右截取准考证号的后4位，取最小号和最大号，如（zkzh2 为最小序号）：

min(right(zkzh,4)) over (partition by kcbh,left(zkzh,4) order by kcbh,left(zkzh,4)) zkzh2

(zkzh3 为最大序号）：

max(right(zkzh,4)) over (partition by kcbh,left(zkzh,4) order by kcbh,left(zkzh,4)) zkzh3

运行结果示例如下：

如图我们可以看到 lzkzh 为左截取的4位准考证分类号，zkzh2为最小号，zkzh3为最大号（本考场内），像第6考场，有两个分类范围，为了便于更直观的显示数据，将其显示为一行，我们可以编写存储过程来实现这一需求。

编写存储过程实现统计

存储过程示例代码如下：

CREATE PROCEDURE [dbo].[kc_zkzhs] @xm_cid uniqueidentifier
AS
BEGINdelete dlzp_kc_zkzhs where xm_cid=@xm_cid insert into dlzp_kc_zkzhs(xm_cid,kcbh,kcmc,zkzhs) select @xm_cid,kcbh,kcmc,'' zkzhs from  dlzp_kc where xm_cid=@xm_cid declare @kcbh nvarchar(50)declare @lzkzh nvarchar(4)declare @zkzh2 nvarchar(4)declare @zkzh3 nvarchar(4)DECLARE _cursor CURSOR FOR  SELECT  kcbh,lzkzh,zkzh2,zkzh3 from v_a order by kcbh,lzkzh,zkzh2,zkzh3OPEN _cursor;  FETCH NEXT FROM _cursor  INTO @kcbh,@lzkzh,@zkzh2,@zkzh3  WHILE @@FETCH_STATUS = 0  BEGIN  update dlzp_kc_zkzhs set zkzhs=zkzhs+@lzkzh+@zkzh2+' - '+@lzkzh+@zkzh3+char(13)+char(10) where xm_cid=@xm_cid  and kcbh=@kcbhFETCH NEXT FROM _cursor  INTO @kcbh,@lzkzh,@zkzh2,@zkzh3   END  CLOSE _cursor;  DEALLOCATE _cursor;select a.kcbh 考场编号,a.kcmc 考场名称,b.kcrs 容纳人数,a.zkzhs 准考证范围  from dlzp_kc_zkzhs a,dlzp_kc b where a.xm_cid=@xm_cid  and a.xm_cid=b.xm_cid and a.kcbh=b.kcbhorder by a.kcbh
end

运行该存储过程可以将多行统计数据合并为单行进行显示，显示结果如下效果：

小结

1、我们的实际操作中有一百多个考场，为避免冗长的代码，示例代码片断并不完整，在这里仅为方便参考。

2、将多行转单行统计可以有很多种方法（比如STUFF函数），本文在这里编写的是存储过程，是一种比较兼容的写法，性能也还不错，否则使用 STUFF 大数据运算（视图）的时候，效果并不理想。更多方法可参考我的文章《MS SQL Server STUFF 函数实战 统计记录行转为列显示》

3、更多partition by 的聚合统计方法可参考我的文章《MS SQL Server partition by 函数实战 统计与输出》

至此 partition by 的实例应用我们就介绍到这里，具体使用中我们还需要灵活掌握。感谢您的阅读，希望本文能够对您有所帮助。


http://www.ppmy.cn/server/165726.html

相关文章

【C++】B2115 密码翻译

【C++】B2115 密码翻译

博客主页&#xff1a; [小ᶻ☡꙳ᵃⁱᵍᶜ꙳] 本文专栏: C 文章目录 &#x1f4af;前言&#x1f4af;题目解析&#x1f4af;1. 老师的做法代码实现&#xff1a;思路解析&#xff1a; &#x1f4af;2. 我的做法代码实现&#xff1a;思路分析&#xff1a; &#x1f4af;3. 老师…
阅读更多...
Linux权限大揭秘：深入理解系统安全

Linux权限大揭秘：深入理解系统安全

W...Y的主页 &#x1f60a; 代码仓库分享&#x1f495; &#x1f354;前言&#xff1a; 在之前的内容中&#xff0c;我们了解了Linux中的一些简单权限&#xff0c;如root超级账号与普通账户的区别、Linux文件权限管理、文件类型和访问权限以及许多关于修改权限的指令及其规则…
阅读更多...
深入解析 Chrome 浏览器的多进程架构：标签页是进程还是线程？（中英双语）

深入解析 Chrome 浏览器的多进程架构：标签页是进程还是线程？（中英双语）

深入解析 Chrome 浏览器的多进程架构&#xff1a;标签页是进程还是线程&#xff1f; 1. 引言 Google Chrome 作为全球最流行的浏览器之一&#xff0c;以其稳定性、安全性和多任务处理能力而闻名。而其高效的表现&#xff0c;很大程度上归功于其独特的多进程架构&#xff08;M…
阅读更多...
一篇关于高等数理统计结合机器学习论文的撰写（如何撰写？）

一篇关于高等数理统计结合机器学习论文的撰写（如何撰写？）

前言 在大学或者研究生阶段&#xff0c;大家可能都会遇到一个问题就是&#xff0c;在上高等数理统计课程时&#xff0c;老师总会让同学们写一些大作业&#xff0c;比如论文什么的&#xff0c;接下来我会从计算机领域的角度&#xff0c;带领大家开启一篇从0到1的高等数理统计文…
阅读更多...
在Ubuntu上使用Docker部署DeepSeek

在Ubuntu上使用Docker部署DeepSeek

在Ubuntu上使用Docker部署DeepSeek&#xff0c;并确保其可以访问公网网址进行对话&#xff0c;可以按照以下步骤进行&#xff1a; 一、安装Docker 更新Ubuntu的软件包索引&#xff1a; sudo apt-get update安装必要的软件包&#xff0c;这些软件包允许apt通过HTTPS使用存储库…
阅读更多...
Mac M1 源码安装FFmpeg，开启enable-gpl 和 lib x264

Mac M1 源码安装FFmpeg，开启enable-gpl 和 lib x264

1、第一步&#xff1a;下载并安装minicoda curl -O https://repo.anaconda.com/miniconda/Miniconda3-latest-MacOSX-arm64.shsh Miniconda3-latest-MacOSX-arm64.sh2、第二步&#xff1a;安装必要的依赖 conda install -c conda-forge gcc make nasm yasm3、第三步&#xff…
阅读更多...
LeetCode Java面试刷题笔记汇总

LeetCode Java面试刷题笔记汇总

LeetCode Java刷题笔记汇总&#xff0c;按照类型刷题效率更高。刷题前需要先学习数据结构与算法的基础知识&#xff1a;Java 数据结构与算法。 大厂面试算法题有一定的运气成分&#xff0c;有可能你刷的比较少&#xff0c;但是遇到会的题就进去了&#xff0c;也有可能你刷的比…
阅读更多...
Unity3D 切线空间及其应用详解

Unity3D 切线空间及其应用详解

引言 在Unity3D中&#xff0c;切线空间&#xff08;Tangent Space&#xff09;是一个非常重要的概念&#xff0c;尤其在法线贴图&#xff08;Normal Mapping&#xff09;和光照计算中扮演着关键角色。理解切线空间的概念及其应用&#xff0c;能够帮助我们更好地实现高质量的图…
阅读更多...
最新文章

Copyright @ 2022~2023