--回字形方阵的SQL解法
--作者：do熊
--日期：2008-02-21
--执行方式（参数必须为整数的平方，但大小并不是无限制的）
--exec sp_Test 2500
--================================================
create procedure sp_Test
@n int
as
begin
declare @layer int, @width int, @spaces varchar(16) --维度，元素宽度，元素前导空格
set @layer=sqrt(@n)
if @layer*@layer<>@n
begin
raiserror('%d is not a square number!', 16, 1, @n)
return 1
end
select @width=len(@n)+1, @spaces=space(@width-1)
declare @sql varchar(max), @i int, @lwidth varchar(16), @sql2 varchar(max), @sql3 varchar(max)
select @lwidth=rtrim(@layer*@width)
, @sql='declare @1 varchar('+@lwidth+')'
, @sql2=char(13)+char(10)+'select @1=SPACE('+@lwidth+')'
, @sql3=char(13)+char(10)+'print @1'
, @i=2
while @i<=@layer
select @sql=@sql+', @'+rtrim(@i)+' varchar('+@lwidth+')'
, @sql2=@sql2+', @'+rtrim(@i)+'=SPACE('+@lwidth+')'
, @sql3=@sql3+char(13)+char(10)+'print @'+rtrim(@i)
, @i=@i+1
set @sql=@sql+@sql2
declare @x int, @y int, @upper int, @loop int, @ii int, @d int
select @x=0, @y=1, @upper=@layer, @i=1, @loop=0, @d=0
while @i<=@n
begin
set @ii=1
if @d=0 -- down
begin
select @x=@x+1
, @sql=@sql+char(13)+char(10)+'select @'+rtrim(@x)
+'=stuff(@'+rtrim(@x)+', '+rtrim(@y)+', '+rtrim(@width)+', right('''+@spaces+rtrim(@i)+''', '+rtrim(@width)+'))'
, @i=@i+1, @ii=@ii+1
while @ii<=@upper
select @x=@x+1
, @sql=@sql+', @'+rtrim(@x)+'=stuff(@'+rtrim(@x)+', '+rtrim(@y)+', '+rtrim(@width)+', right('''+@spaces+rtrim(@i)+''', '+rtrim(@width)+'))'
, @i=@i+1, @ii=@ii+1
end
else if @d=1 -- right
begin
select @sql2='', @y=@y+@width
while @ii<=@upper
select @sql2=@sql2+right(@spaces+rtrim(@i), @width)
, @i=@i+1, @ii=@ii+1
select @sql=@sql+char(13)+char(10)+'select @'+rtrim(@x)
+'=stuff(@'+rtrim(@x)+', '+rtrim(@y)+', '+rtrim(@width*@upper)+', '''+@sql2+''')'
, @y=@y+@width*(@upper-1)
end
else if @d=2 -- up
begin
select @x=@x-1
, @sql=@sql+char(13)+char(10)+'select @'+rtrim(@x)
+'=stuff(@'+rtrim(@x)+', '+rtrim(@y)+', '+rtrim(@width)+', right('''+@spaces+rtrim(@i)+''', '+rtrim(@width)+'))'
, @i=@i+1, @ii=@ii+1
while @ii<=@upper
select @x=@x-1
, @sql=@sql+', @'+rtrim(@x)+'=stuff(@'+rtrim(@x)+', '+rtrim(@y)+', '+rtrim(@width)+', right('''+@spaces+rtrim(@i)+''', '+rtrim(@width)+'))'
, @i=@i+1, @ii=@ii+1
end
else if @d=3 -- left
begin
select @sql2=''
while @ii<=@upper
select @sql2=right(@spaces+rtrim(@i), @width)+@sql2
, @i=@i+1, @ii=@ii+1
select @y=@y-@width*@upper
, @sql=@sql+char(13)+char(10)+'select @'+rtrim(@x)
+'=stuff(@'+rtrim(@x)+', '+rtrim(@y)+', '+rtrim(@width*@upper)+', '''+@sql2+''')'
end
select @d=(@d+1)%4, @upper=@upper-@d%2
end -- print @sql+@sql3
exec(@sql+@sql3)
end
go exec sp_Test 6400