目录

需求

输出效果

范例运行环境

表及视图样本设计

功能实现

生成考场数据

生成重复的SQL语句

封装为统计视图

编写存储过程实现统计

小结


需求

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

(1)上传考场分布信息的EXCEL,导入生成考场信息数据。

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

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

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

输出效果

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

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

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

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

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

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

范例运行环境

操作系统: Windows Server 2019 DataCenter

数据库:Microsoft SQL Server 2016

.netFramework 4.7.1

开发工具:VisualStudio 2019 C#

表及视图样本设计

考场表 [dlzp_kc] 设计如下:

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

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

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

功能实现

生成考场数据

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

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

生成重复的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 = "\r\nselect top {0} '{1}' kcbh,'{2}' kcmc,{3} kcrs,ROW_NUMBER() over(order by kcbh) zwh from v_ypz \r\n {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

运行结果示例如下:

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

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

编写存储过程实现统计

存储过程示例代码如下:

CREATE PROCEDURE [dbo].[kc_zkzhs] 
		   @xm_cid uniqueidentifier
AS
BEGIN
	delete 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,zkzh3
	
	OPEN _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=@kcbh
		
		FETCH 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.kcbh
	order by a.kcbh
end

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

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

 

小结

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

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

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

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

08-21 00:23