分配考场SQL实现
2021/10/11 19:16:28
本文主要是介绍分配考场SQL实现,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
/* 1.每个考生前后左右尽量不同班,若不满足可以降低要求 */ --考场数据设置 CREATE TABLE #tmp ( kc VARCHAR(10), z INT, h INT ) --待分配学生数据 CREATE TABLE #rst ( id INT IDENTITY(1,1), bj VARCHAR(10), xm VARCHAR(10), xh VARCHAR(10), kc VARCHAR(10), zw VARCHAR(10), z INT, h INT, ) --添加考生信息 INSERT INTO #rst(bj,xm,xh) SELECT 班级,姓名,学号 FROM fenkaoc ORDER BY 班级,学号 --生成5纵7横的21个考场 INSERT INTO #tmp(kc,z,h) SELECT CONVERT(VARCHAR(10),number)+'#',5,7 FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND 21 --生成5纵5横的5个考场 INSERT INTO #tmp(kc,z,h) SELECT CONVERT(VARCHAR(10),21+number)+'#',5,6 FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND 5 --生成考场座位 SELECT IDENTITY(INT,1,1) AS id, * INTO #tmp2 FROM ( SELECT a.kc,a.z,a.h,b.number AS i,c.number AS j FROM #tmp a OUTER APPLY (SELECT * FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND z) b OUTER APPLY (SELECT * FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND h) c ) a SELECT IDENTITY(INT,1,1) AS id,*, CONVERT(VARCHAR(50),null) AS k1, CONVERT(VARCHAR(50),null) AS k2, CONVERT(VARCHAR(50),null) AS k3, CONVERT(VARCHAR(50),null) AS k4, CONVERT(VARCHAR(50),null) AS k5, CONVERT(VARCHAR(50),null) AS k6, CONVERT(VARCHAR(50),null) AS k7, CONVERT(VARCHAR(50),null) AS k8, CONVERT(VARCHAR(50),null) AS k9, CONVERT(VARCHAR(50),null) AS k10, CONVERT(VARCHAR(50),null) AS x1, CONVERT(VARCHAR(50),null) AS x2, CONVERT(VARCHAR(50),null) AS x3, CONVERT(VARCHAR(50),null) AS x4, CONVERT(VARCHAR(50),null) AS x5, CONVERT(VARCHAR(50),null) AS x6, CONVERT(VARCHAR(50),null) AS x7, CONVERT(VARCHAR(50),null) AS x8, CONVERT(VARCHAR(50),null) AS x9, CONVERT(VARCHAR(50),null) AS x10 INTO #tmp3 FROM #tmp2 PIVOT ( MAX(id) FOR i IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]) ) p --开始分配 DECLARE @i INT DECLARE @j INT DECLARE @r INT DECLARE @zw INT DECLARE @h INT DECLARE @bj VARCHAR(50) DECLARE @bj1 VARCHAR(50) DECLARE @bj2 VARCHAR(50) DECLARE @bj3 VARCHAR(50) DECLARE @kc VARCHAR(50) DECLARE @xh VARCHAR(50) DECLARE @sql NVARCHAR(4000) SET @i=1 SELECT @r=MAX(id) FROM #tmp3 WHILE @i<=@r BEGIN SET @j=1 WHILE @j<=10 BEGIN SET @bj='' SET @bj1='' SET @bj2='' SET @bj3='' SET @kc='' SET @xh='' SET @sql='SELECT @kc=kc,@zw=['+CONVERT(NVARCHAR(10),@j)+'],@h=j FROM #tmp3 WHERE id=@i' EXEC sp_executesql @sql,N'@i int,@zw int output,@kc VARCHAR(50) output,@h int output',@i,@zw OUTPUT,@kc OUTPUT,@h OUTPUT IF @zw IS NULL BEGIN BREAK END --前 SET @sql='SELECT @bj1=[k'+CONVERT(NVARCHAR(10),@j)+'] FROM #tmp3 WHERE id=@i-1 and kc=@kc' EXEC sp_executesql @sql,N'@i int,@kc VARCHAR(50),@bj1 varchar(50) output',@i,@kc,@bj1 OUTPUT --左 IF @j>1 BEGIN SET @sql='SELECT @bj2=[k'+CONVERT(NVARCHAR(10),@j-1)+'] FROM #tmp3 WHERE id=@i and kc=@kc' EXEC sp_executesql @sql,N'@i int,@kc VARCHAR(50),@bj2 varchar(50) output',@i,@kc,@bj2 OUTPUT END --右 SET @sql='SELECT @bj3=[k'+CONVERT(NVARCHAR(10),@j+1)+'] FROM #tmp3 WHERE id=@i and kc=@kc' EXEC sp_executesql @sql,N'@i int,@kc VARCHAR(50),@bj3 varchar(50) output',@i,@kc,@bj3 OUTPUT SET @bj1=ISNULL(@bj1,'') SET @bj2=ISNULL(@bj2,'') SET @bj3=ISNULL(@bj3,'') SELECT TOP 1 @xh=xh,@bj=bj FROM #rst WHERE bj NOT IN(@bj1,@bj2,@bj3) AND kc IS NULL ORDER BY NEWID() IF ISNULL(@xh,'')='' SELECT TOP 1 @xh=xh,@bj=bj FROM #rst WHERE bj NOT IN(@bj1,@bj2) AND kc IS NULL ORDER BY NEWID() IF ISNULL(@xh,'')='' SELECT TOP 1 @xh=xh,@bj=bj FROM #rst WHERE bj NOT IN(@bj1) AND kc IS NULL ORDER BY NEWID() IF ISNULL(@xh,'')='' SELECT TOP 1 @xh=xh,@bj=bj FROM #rst WHERE kc IS NULL ORDER BY NEWID() UPDATE #rst SET kc=@kc,z=@j,h=@h,zw=@zw WHERE xh=@xh SET @sql='UPDATE #tmp3 SET [k'+CONVERT(NVARCHAR(10),@j)+']=@bj,[x'+CONVERT(NVARCHAR(10),@j)+']=@xh where id=@i' EXEC sp_executesql @sql,N'@i int,@bj VARCHAR(50),@xh VARCHAR(50)',@i,@bj,@xh SET @j=@j+1 END SET @i=@i+1 END --查询结果 SELECT * FROM #tmp3 WHERE kc='1#' SELECT * FROM #rst WHERE kc='26#' DROP TABLE #rst DROP TABLE #tmp DROP TABLE #tmp2 DROP TABLE #tmp3
这篇关于分配考场SQL实现的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-07-02springboot项目无法注册到nacos-icode9专业技术文章分享
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)