分配考场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