4N+2阶幻方SQL实现

CREATE TABLE #tmp
(
	x CHAR(1),
	i INT,
	j INT,
	val INT,
	   
)

DECLARE @n INT =4*4+2
DECLARE @i INT  
DECLARE @j INT  
DECLARE @k INT  
DECLARE @m INT
DECLARE @a INT  
DECLARE @b INT  
DECLARE @c INT  
DECLARE @d INT  
DECLARE @SQL VARCHAR(max)

--分成ABCD4个象限,按2N+1阶幻方填写
SET @k=(@n-2)/4 
SET @m= @n/2
SET @i=1
SET @j=@m/2+1
EXEC sp_msjz2 'A',@i,@j,1,@m
EXEC sp_msjz2 'B',@i,@j,1,@m
EXEC sp_msjz2 'C',@i,@j,1,@m
EXEC sp_msjz2 'D',@i,@j,1,@m
 

UPDATE #tmp SET 
i=i+IIF(x IN('A','B'),0,@m),
j=j+IIF(x IN('A','C'),0,@m),
val=val+CASE 
WHEN x='A' THEN 0 
WHEN x='B' THEN 2
WHEN x='C' THEN 3
WHEN x='D' THEN 1
END*@m*@m 


--在A象限的中间行,中间格开始,按自左向右的方向,标出k格,A象限的其它行则标出最左边的k格,将这些格和C象限相对位置上的数,互换位置
--在B象限任一行的中间格,自右向左,标出k-1列(注:6阶幻方由于k-1=0,所以不用再作BD象限的数据交换)将B象限标出的这些数,和D,象限相对位置上的数进行交换
UPDATE  #tmp SET i=IIF(i>@m,i-@m,@m+i) WHERE j<=@k AND i NOT IN (@j,@j+@m) AND  x IN  ('A','C')
UPDATE  #tmp SET i=IIF(i>@m,i-@m,@m+i) WHERE j>@k AND j<@m AND i IN (@j,@j+@m) AND  x IN  ('A','C')

IF @K-1>0
BEGIN 
	UPDATE  #tmp SET i=IIF(i>@m,i-@m,@m+i) WHERE j>=@k+@m+1 AND j<2*@k+@m AND  x IN  ('B','D') 
END  
 
UPDATE #tmp SET x='' 


--动态行列转换 
SET @SQL=(
SELECT ','+QUOTENAME(i) FROM #tmp  
GROUP BY i
ORDER BY  i
FOR XML PATH('')  
)

SET @SQL='
SELECT  i'+@SQL+' FROM #tmp
PIVOT
(
	MAX(val)
	FOR j IN ('+STUFF(@SQL,1,1,'')+')
)b
ORDER BY i
'

EXEC(@sql)
 

DROP TABLE #tmp




--2N+1
CREATE PROCEDURE sp_msjz2(@xx CHAR(1), @i INT ,@j INT ,@v INT,@n INT ) 
AS
BEGIN 
	DECLARE @x INT  
	DECLARE @y INT  
  
	--2.从第一行中间开始
	INSERT INTO #tmp VALUES(@xx,@i,@j,@v)

	--3.循环填写
	WHILE @v<@n * @n
	BEGIN 
			Set @v=@v+1
			
			--记录上次的位置
	 		Set @X=@I
			Set @Y=@J
			
			--斜向上
			Set @I=@I-1
			Set @J=@J-1
			
			--如果超界
			IF @I<1
				Set @I=@N
			IF @J<1
				Set @J=@N

			--如果存在
			IF EXISTS(SELECT 1 FROM  #tmp WHERE x=@xx and i=@i AND  j=@j)
			Begin
				Set @I=@X+1
				Set @J=@Y
			End

			INSERT INTO #tmp VALUES(@xx,@i,@j,@v)
	END
END