企业官网建设流程全解析

WordPress输入密码可见,江门网站优化经验,广告营销模式,公司网站管理目录 需求 输出效果 范例运行环境 表及视图样本设计 功能实现 生成考场数据 生成重复的SQL语句 封装为统计视图 编写存储过程实现统计 小结 需求 假设有若干已分配准考证号的考生#xff0c;准考证号示例#xff08;01010001#xff09;共计8位#xff0c;前4位…目录需求输出效果范例运行环境表及视图样本设计功能实现生成考场数据生成重复的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] 设计如下序号字段名类型说明备注1xm_ciduniqueidentifier所属项目ID比如某一考试项目2kcbhnvarchar(6)考场编号按固定位补位排序3kcmcnvarchar(50)考场名称4kcrssmallint考场人数含准考证号的考生视图 v_ypz 设计如下序号字段名类型说明备注1xm_ciduniqueidentifier所属项目ID比如某一考试项目2zkzhchar(8)准考证号固定8位根据设计 v_ypz 数据集记录大于等于考场记录数因此使用左连接以显示考场座位数不足的情况统计。准考证号考场范围分布情况表dlzp_kc_zkzhs 设计如下序号字段名类型说明备注1xm_ciduniqueidentifier所属项目ID比如某一考试项目2kcbhnvarchar(6)考场编号3kcmcnvarchar(50)考场名称冗余字段4zkzhsnvarchar(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.xhb.xh ;该程序片断可辅助我们生成想要的SQL语句以避免重复劳动生成的最终结果如下select b.zkzh,a.* from (select xm,sfzh,ROW_NUMBER() over(order by zkzh) xh,zkzh from v_ypz where xm_cid16286689-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.xhb.xh在主表 v_ypz 按准考证号升序生成总序号xh ROW_NUMBER() over(order by zkzh) xh考场明细视图按考场编号升序生成总序号xhROW_NUMBER() over(order by kcbh) as xh其中每个考场的则按考场编号生成座位号zwhROW_NUMBER() over(order by kcbh) zwh最后我们通过左连接即可得到我们想要的分配明细情况结果。封装为统计视图将上述分配明细查询进一步进行统计准考证号范围封装为视图 v_aSQL 语句如下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_cid16286689-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.xhb.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 BEGIN delete dlzp_kc_zkzhs where xm_cidxm_cid insert into dlzp_kc_zkzhs(xm_cid,kcbh,kcmc,zkzhs) select xm_cid,kcbh,kcmc, zkzhs from dlzp_kc where xm_cidxm_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 zkzhszkzhslzkzhzkzh2 - lzkzhzkzh3char(13)char(10) where xm_cidxm_cid and kcbhkcbh 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_cidxm_cid and a.xm_cidb.xm_cid and a.kcbhb.kcbh order by a.kcbh end运行该存储过程可以将多行统计数据合并为单行进行显示显示结果如下效果小结1、我们的实际操作中有一百多个考场为避免冗长的代码示例代码片断并不完整在这里仅为方便参考。2、将多行转单行统计可以有很多种方法比如STUFF函数本文在这里编写的是存储过程是一种比较兼容的写法性能也还不错否则使用 STUFF 大数据运算视图的时候效果并不理想。更多方法可参考我的文章《MS SQL Server STUFF 函数实战 统计记录行转为列显示》3、更多partition by 的聚合统计方法可参考我的文章《MS SQL Server partition by 函数实战 统计与输出》至此 partition by 的实例应用我们就介绍到这里具体使用中我们还需要灵活掌握。感谢您的阅读希望本文能够对您有所帮助。