魔方算法

别人的好东西  收藏了 听好玩的

 

有1－16的数字
在SQL中输出
输出的样式是
1 12 11 10
2 13 16  9
3 14 15  8
4  5  6  7

--================================================
--绕圈的SQL算法（感觉很笨，不知道有没有更好的算法）
--作者：冷箫轻笛
--日期：2008-02-21
--执行方式（参数必须为整数的平方，但大小并不是无限制的）
--exec raoquan 2500
--================================================

create proc raoquan
(
@num int
)
as
begin

set nocount on

declare @i int
set @i = SQRT(@num)

if CEILING(@i) <> @i
   return

declare @table varchar(8000)
declare @insert varchar(8000)
declare @update varchar(8000)
declare @print varchar(8000)

select @insert = '',@update = '',@print = ''

select @table = '
create table #t
('

declare @k int
select @k = 1
while (@k <= @i)
begin
    select @table = @table + '
[' + cast(@k as varchar) + '] int,'
    select @insert = @insert + 'insert into #t([1]) select ' + cast(@k as varchar) + ' '
    select @k = @k + 1
end

select @table = reverse(stuff(reverse(@table),1,1,''))

select @table = @table + '
)
'

select @update = '
declare @r int
declare @c int
select @r = SQRT(' + cast(@num as varchar) + '),@c = 1

declare @i int
select @i = SQRT(' + cast(@num as varchar) + ') + 1
declare @s1 char(1)
select @s1 = ''+''
declare @s2 char(1)
select @s2 = ''c''
declare @s3 int --用于计算符号
select @s3 = 0
declare @len int
select @len = SQRT(' + cast(@num as varchar) + ')
declare @len2 int
select @len2 = 0


while (@len > 0)
begin
    if @s3 % 2 = 0
        select @len = @len - 1
    select @len2 = @len

    while(@len2 > 0)
    begin
        if @s1 = ''+''
        begin
            if     @s2 = ''c''
                select @c = @c + 1
            else
                select @r = @r + 1
        end   
        else
        begin
            if     @s2 = ''c''
                select @c = @c - 1
            else
                select @r = @r - 1
        end
       
        declare @sql varchar(8000)
        select @sql = ''update #t set ['' + cast(@c as varchar) + ''] = '' + cast(@i as varchar) +
                      '' where [1] = '' + cast(@r as varchar)
        exec (@sql)

        select @len2 = @len2 - 1
        select @i = @i + 1

    end

    if @s3/2 % 2 = 1
        select @s1 = ''+''
    else
        select @s1 = ''-''

    if @s2 = ''c''
        select @s2 = ''r''
    else
        select @s2 = ''c''

    select @s3 = @s3 + 1
end

--select * from #t
'

select @print = '
declare @string varchar(8000)
select @string = ''''
declare @conn varchar(1000)
select  @conn =''''
declare @ii int
select @ii = sqrt('+ cast(@num as varchar) + ')
declare @m int
select @m = 1
declare @n int
select @n = 1
while (@ii >= @n)
begin
    select @string = ''declare @conn varchar(1000)
        select  @conn =''
    select @m = 1

    while (@ii >= @m)
    begin
        select @string = @string + '' right( SPACE(len(cast('+cast(@num as varchar)+' as varchar))) + cast(['' + cast(@m as varchar) + ''] as varchar), len(cast('+cast(@num as varchar)+' as varchar)) + 1) +''
        select @m = @m + 1
    end
    select @string = reverse(stuff(reverse(@string),1,1,''''))
    select @string = @string + '' from #t where [1] = '' +  cast(@n as varchar) + ''
    print @conn''
    exec(@string)
    select @n = @n + 1
end
'
exec (@table + @insert + @update + @print)

end

转载于:https://www.cnblogs.com/ruyi/archive/2009/06/24/1510578.html