--================================================-- -- 数字螺旋方阵的SQL函数 -- 作者: Limpire(昨夜小楼) -- 日期: 2008-02-25 --================================================-- CREATE FUNCTION Matrix(@n int,@x int,@y int,@i int,@a bit,@c bit) -- @n: 方阵的阶. -- (@x,@y): 垂直倒置第1象限坐标, 由0开始. -- @i: 初始值. 为null时: 如果是递增方式, 由1开始递增; 如果是递减方式, 由@n的平方开始递减. -- @a: 1为递增方式, 0为递减方式. -- @c: 1为顺时针方向, 0为逆时针方向 RETURNS int ASBEGINdeclare @Layer int,@Return intif @x>@y set @Layer=@y else set @Layer=@x if @Layer>@n-@x-1 set @Layer=@n-@x-1 if @Layer>@n-@y-1 set @Layer=@n-@y-1if @a=1 -- 递增 begin if @i is null set @i=1 if (@c=0 and @x<=@y) or (@c=1 and @x>=@y) set @Return=@x+@y-2*@Layer+(@Layer*@n-power(@Layer,2))*4+@i else set @Return=4*@n-6*@Layer-@x-@y+(@Layer*@n-power(@Layer,2))*4+@i-4 endelse -- 递减 begin if @i is null set @i=power(@n,2) if (@c=0 and @x<=@y) or (@c=1 and @x>=@y) set @Return=2*@Layer-@x-@y-(@Layer*@n-power(@Layer,2))*4+@i else set @Return=power(@n-2*@Layer-2,2)+@x+@y-2*@Layer-power(@n,2)+@i endreturn (@Return)END GO
declare @n int,@x int,@y int,@i int,@a bit,@c bit,@SQL varchar(max) set @n=5 -- 方阵阶 set @i=null -- 无初始 set @a=1 -- 递增方式 set @c=0 -- 逆时针 set @y=0 while @y<@n begin select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select' while @x<@n begin if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' if @y=0 set @SQL=@SQL+'C'+ltrim(@x) set @x=@x+1 end set @y=@y+1 end exec (@SQL) /* C0 C1 C2 C3 C4 ----------- ----------- ----------- ----------- ----------- 1 16 15 14 13 2 17 24 23 12 3 18 25 22 11 4 19 20 21 10 5 6 7 8 9 */
declare @n int,@x int,@y int,@i int,@a bit,@c bit,@SQL varchar(max) set @n=5 -- 方阵阶 set @i=null -- 无初始 set @a=0 -- 递减 set @c=0 -- 逆时针 set @y=0 while @y<@n begin select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select' while @x<@n begin if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' if @y=0 set @SQL=@SQL+'C'+ltrim(@x) set @x=@x+1 end set @y=@y+1 end exec (@SQL) /* C0 C1 C2 C3 C4 ----------- ----------- ----------- ----------- ----------- 25 10 11 12 13 24 9 2 3 14 23 8 1 4 15 22 7 6 5 16 21 20 19 18 17 */
declare @n int,@x int,@y int,@i int,@a bit,@c bit,@SQL varchar(max) set @n=5 -- 方阵阶 set @i=150 -- 初始150 set @a=1 -- 递增 set @c=1 -- 顺时针 set @y=0 while @y<@n begin select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select' while @x<@n begin if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' if @y=0 set @SQL=@SQL+'C'+ltrim(@x) set @x=@x+1 end set @y=@y+1 end exec (@SQL) /* C0 C1 C2 C3 C4 ----------- ----------- ----------- ----------- ----------- 150 151 152 153 154 165 166 167 168 155 164 173 174 169 156 163 172 171 170 157 162 161 160 159 158 */
declare @n int,@x int,@y int,@i int,@a bit,@c bit,@SQL varchar(max) set @n=5 -- 方阵阶5 set @i=150 -- 初始150 set @a=0 -- 递减 set @c=1 -- 顺时针 set @y=0 while @y<@n begin select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select' while @x<@n begin if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')' if @y=0 set @SQL=@SQL+'C'+ltrim(@x) set @x=@x+1 end set @y=@y+1 end exec (@SQL) /* C0 C1 C2 C3 C4 ----------- ----------- ----------- ----------- ----------- 150 149 148 147 146 135 134 133 132 145 136 127 126 131 144 137 128 129 130 143 138 139 140 141 142 */
declare @x int; set @x = 9;with tmp1 as (select top(@x) Row_Number() over(order by id) as x from sysobjects order by id) select ( select convert(char(5),no) from (select x,y,row_number() over(order by c) as no from ( select x,y, case c when x-1 then (x-1)*100+ 0+y when @x-y then (@x-y)*100+10+x when @x-x then (@x-x)*100+20+(@x-y) when y-1 then (y-1)*100+30+(@x-x) end as c from ( select x,y,case when c5<=c6 then c5 else c6 end as c from ( select x,y,case when c1<=c2 then c1 else c2 end as c5, case when c3<=c4 then c3 else c4 end as c6 from ( select x,y,x-1 as c1,@x-y as c2,@x-x as c3,y-1 as c4 from( select a.x,b.x as y from tmp1 a,tmp1 b ) c ) d ) e ) f ) g ) h where h.y=z.x order by h.x for xml path('') ) from tmp1 z
10楼的方法很牛! 不过还有一点瑕疵:如果输入的不是完全平方数,就不能正确得出结果, 所以可以改一下输入为 行数(列数)--================================================ --绕圈的SQL算法(感觉很笨,不知道有没有更好的算法) --作者:冷箫轻笛 --日期:2008-02-21 --执行方式(参数必须为整数的平方,但大小并不是无限制的) --exec raoquan 2500 --修改:山之魂 --日期:2008-03-05 --执行方式(参数改为行数,可以输1-79的自然数,大了会出错,) --exec raoquan 79 --机子慢的可能要很久 --================================================create alter proc raoquan ( @i int ) as beginset nocount ondeclare @num int set @num=@i*@iif CEILING(@i) <> @i returndeclare @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 endselect @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 = 1declare @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
declare @x int, @y int; set @x = 9; set @y = 5;with tmp1 as (select top(case when @x>@y then @x else @y end) Row_Number() over(order by id) as x from sysobjects order by id) select top(@y) ( select convert(char(5),no) from (select x,y,row_number() over(order by c) as no from ( select x,y, case c when x-1 then (x-1)*100+ 0+y when @y-y then (@y-y)*100+10+x when @x-x then (@x-x)*100+20+(@y-y) when y-1 then (y-1)*100+30+(@x-x) end as c from ( select x,y,case when c5 <=c6 then c5 else c6 end as c from ( select x,y,case when c1 <=c2 then c1 else c2 end as c5, case when c3 <=c4 then c3 else c4 end as c6 from ( select x,y,x-1 as c1,@y-y as c2,@x-x as c3,y-1 as c4 from( select a.x,b.x as y from tmp1 a,tmp1 b where a.x<=@x and b.x<=@y ) c ) d ) e ) f ) g ) h where h.y=z.x order by h.x for xml path('') ) from tmp1 z /*----------------------------------------- 1 24 23 22 21 20 19 18 17 2 25 40 39 38 37 36 35 16 3 26 41 42 43 44 45 34 15 4 27 28 29 30 31 32 33 14 5 6 7 8 9 10 11 12 13 (5 行受影响) */
算你狠~~ declare @x int, @y int; set @x = 9; set @y = 5;with tmp1 as (select top(case when @x>@y then @x else @y end) Row_Number() over(order by id) as x from sysobjects order by id) select top(@y) ( select convert(char(5),no) from (select x,y,row_number() over(order by c) as no from ( select x,y, case c when x-1 then (x-1)*100+ 0+y when @y-y then (@y-y)*100+10+x when @x-x then (@x-x)*100+20+(@y-y) when y-1 then (y-1)*100+30+(@x-x) end as c from ( select x,y,case when c5 <=c6 then c5 else c6 end as c from ( select x,y,case when c1 <=c2 then c1 else c2 end as c5, case when c3 <=c4 then c3 else c4 end as c6 from ( select x,y,x-1 as c1,@y-y as c2,@x-x as c3,y-1 as c4 from( select a.x,b.x as y from tmp1 a,tmp1 b where a.x<=@x and b.x<=@y ) c ) d ) e ) f ) g ) h where h.y=z.x order by h.x for xml path('') ) from tmp1 z
搞了4个小时,终于有结果 declare @n int,@k int,@maxlayer int,@i int,@j int,@il int,@jl int declare @lstr varchar(8000),@lstr1 char(10) declare @tmp table (lstr varchar(8000)) declare @tmp1 table (lstr varchar(8000)) select @n=5 select @maxlayer=ceiling(@n/2.0) --算出数组元素i,j是属于哪一层 --第k层长度为 4*((n-2(k-1))-1)=4*(n-2k+1) --第k层应减去的长度为 4*(n-2*1+1)+4*(n-2*2+1)+4*(n-2(k-1)+1)=4*((k-1)*(n+1)-2*(1+2+...k-1)) -- =4*((k-1)*(n+1)-k*(k-1)) -- =4*(k-1)*(n+1-k)=4*(k-1)*(n-k+1) --select 4*(@k-1)*(@n-@k+1) --第k层的每边长度n-2k+2 --@i的层数 set nocount on select @i=1,@j=1 while @i<=@n begin --要求规则 set @lstr='' while @j<=@n begin select @il=case when @i<=@maxlayer then @i else @n-@i+1 end,@jl=case when @j<=@maxlayer then @j else @n-@j+1 end if @il>@jl set @k=@jl else set @k=@il
select @lstr1=convert(char(10),case when @j=@k then @i-@k+1+4*(@k-1)*(@n-@k+1) --left when @n-@i+1=@k then 4*(@k-1)*(@n-@k+1)+@n-2*@k+2+@j-@k --bottom when @n-@j+1=@k then 4*@k*(@n-@k)-(@n-2*@k+1)-@i+@k+1 --right else 4*@k*(@n-@k)-@j+@k+1 --top end) if @lstr='' set @lstr=@lstr1 else set @lstr=@lstr+','+@lstr1
set @j=@j+1
end insert into @tmp values(@lstr) --改变一下规则,顺时针 set @j=1 set @lstr='' while @j<=@n begin select @il=case when @i<=@maxlayer then @i else @n-@i+1 end,@jl=case when @j<=@maxlayer then @j else @n-@j+1 end if @il>@jl set @k=@jl else set @k=@il
select @lstr1=convert(char(10),case when @i=@k then 4*(@k-1)*(@n-@k+1)+@j-@k+1 --top when @n-@j+1=@k then 4*(@k-1)*(@n-@k+1)+@n-2*@k+2+@i-@k --right when @n-@i+1=@k then 4*@k*(@n-@k)-(@n-2*@k+1)-@j+@k+1 --bottom when @j=@k then 4*@k*(@n-@k)-@i+@k+1 --left end) if @lstr='' set @lstr=@lstr1 else set @lstr=@lstr+','+@lstr1
set @j=@j+1
end insert into @tmp1 values(@lstr)
select @i=@i+1,@j=1
end select * from @tmp select * from @tmp1
当行列数太多的时候,可以适当增加when @x-y then (@x-y)*100+10+x附近那几行的常量的值, 例如改成when @y-y then (@y-y)*10000+1000+x, 当然,要一劳永逸的话,把这一段分开三列再排序就可以了,懒得去改啦,有兴趣的朋友自己试吧。
ALTER PROCEDURE dbo.test3 @Count int as declare @T Table (id int,x int ,y int) Declare @i int set @i =power(@count,0.5) declare @j int--填充数量 set @j=1 declare @Direction int--填充方向 set @Direction=1 declare @x int--横坐标值 declare @y int--纵坐标值 set @x=1 set @y=1 declare @index int--不同方向填充的个数 set @index =1 while @j<=@count begin insert @t select @j,@x,@y set @j=@j+1 if @index =@i--该方向填充满时,填充方向改变 begin set @Direction=@Direction % 4 +1 if @Direction=2 or @Direction=4 set @i=@i-1--当填充方向为横向时,该方向填充个数减1 set @Index =0 end if @Direction=1 set @y=@y+1--从上往下填充时,Y坐标递增,X坐标不变 if @Direction=2 set @X=@X+1--从左住右填充时,X坐标递增,Y坐标不变 if @Direction=3 set @y=@Y-1--从上往下填充时,Y坐标递减,X坐标不变 if @Direction=4 set @x=@X-1--从左住右填充时,X坐标递减,Y坐标不变 set @index =@index+1 end set @y=1 declare @str varchar(2000) Declare @TT Table (Re varchar(2000)) while @j <=power(@count,0.5) begin set @Str='' select @str=@str +left(cast(id as varchar(10))+' ',6) from @t where y=@y order by x insert @TT select @str as re set @y=@y+1 end select * from @TT
1 12 11 10
2 13 16 9
3 14 15 8
4 5 6 7
into tselect * from t
select 2, 13, 16, 9 union all
select 3, 14, 15, 8 union all
select 4, 5, 6, 7
这个不能这样直接的输出 是要经过算法才行的 比如说现在是16个 那还有可能是 25个 36个49个........
--================================================
--绕圈的SQL算法(感觉很笨,不知道有没有更好的算法)
--作者:冷箫轻笛
--日期:2008-02-21
--执行方式(参数必须为整数的平方,但大小并不是无限制的)
--exec raoquan 2500
--================================================create proc raoquan
(
@num int
)
as
beginset nocount ondeclare @i int
set @i = SQRT(@num)if CEILING(@i) <> @i
returndeclare @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
endselect @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 = 1declare @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
--测试
exec raoquan 400/*
1 76 75 74 73 72 71 70 69 68 67 66 65 64 63 62 61 60 59 58
2 77 144 143 142 141 140 139 138 137 136 135 134 133 132 131 130 129 128 57
3 78 145 204 203 202 201 200 199 198 197 196 195 194 193 192 191 190 127 56
4 79 146 205 256 255 254 253 252 251 250 249 248 247 246 245 244 189 126 55
5 80 147 206 257 300 299 298 297 296 295 294 293 292 291 290 243 188 125 54
6 81 148 207 258 301 336 335 334 333 332 331 330 329 328 289 242 187 124 53
7 82 149 208 259 302 337 364 363 362 361 360 359 358 327 288 241 186 123 52
8 83 150 209 260 303 338 365 384 383 382 381 380 357 326 287 240 185 122 51
9 84 151 210 261 304 339 366 385 396 395 394 379 356 325 286 239 184 121 50
10 85 152 211 262 305 340 367 386 397 400 393 378 355 324 285 238 183 120 49
11 86 153 212 263 306 341 368 387 398 399 392 377 354 323 284 237 182 119 48
12 87 154 213 264 307 342 369 388 389 390 391 376 353 322 283 236 181 118 47
13 88 155 214 265 308 343 370 371 372 373 374 375 352 321 282 235 180 117 46
14 89 156 215 266 309 344 345 346 347 348 349 350 351 320 281 234 179 116 45
15 90 157 216 267 310 311 312 313 314 315 316 317 318 319 280 233 178 115 44
16 91 158 217 268 269 270 271 272 273 274 275 276 277 278 279 232 177 114 43
17 92 159 218 219 220 221 222 223 224 225 226 227 228 229 230 231 176 113 42
18 93 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 112 41
19 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 40
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
*/
reverse(stuff(reverse(@table),1,1,''))
-->
rtrim(@table)
但是没有更好的思路
reverse(stuff(reverse(@table),1,1,''))
是用来去掉最后的一个"+"号,
rtrim(@table)不太行,呵呵
--绕圈的SQL算法(感觉很笨,不知道有没有更好的算法)
--作者:jht
--日期:2008-02-21
--执行方式(参数必须为整数的平方,但大小并不是无限制的)
--set a = 20
--================================================declare @a int,@k int,@s varchar(1000),@i int
declare @r int,@c int,@rf int,@cf int
select @a=20,@k = 0
create table # (id int)
while @k < @a
begin
insert #(id) select @k
set @s = 'alter table # add c'+rtrim(@k)+' int' exec(@s)
set @k = @k +1
end
select @r =-1,@c = 0,@rf = 1,@cf = 0,@i = 0
while @a > 0
begin
set @k = 0
while @k <@a
begin
select @k = @k +1,@i= @i+1,@r = @r+@rf,@c = @c +@cf
set @s = 'update # set c'+rtrim(@c)+'= '+rtrim(@i)+' where id= '+rtrim(@r) exec(@s)
print @s
end
if @rf = 1 and @cf = 0 select @rf = 0,@cf = 1
else if @rf = 0 and @cf = 1 select @rf = -1,@cf = 0
else if @rf = -1 and @cf = 0 select @rf = 0,@cf = -1
else if @rf = 0 and @cf = -1 select @rf = 1,@cf = 0
if @rf = 0 set @a = @a-1
end
select * from #
drop table #
/*
id c0 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0 1 76 75 74 73 72 71 70 69 68 67 66 65 64 63 62 61 60 59 58
1 2 77 144 143 142 141 140 139 138 137 136 135 134 133 132 131 130 129 128 57
2 3 78 145 204 203 202 201 200 199 198 197 196 195 194 193 192 191 190 127 56
3 4 79 146 205 256 255 254 253 252 251 250 249 248 247 246 245 244 189 126 55
4 5 80 147 206 257 300 299 298 297 296 295 294 293 292 291 290 243 188 125 54
5 6 81 148 207 258 301 336 335 334 333 332 331 330 329 328 289 242 187 124 53
6 7 82 149 208 259 302 337 364 363 362 361 360 359 358 327 288 241 186 123 52
7 8 83 150 209 260 303 338 365 384 383 382 381 380 357 326 287 240 185 122 51
8 9 84 151 210 261 304 339 366 385 396 395 394 379 356 325 286 239 184 121 50
9 10 85 152 211 262 305 340 367 386 397 400 393 378 355 324 285 238 183 120 49
10 11 86 153 212 263 306 341 368 387 398 399 392 377 354 323 284 237 182 119 48
11 12 87 154 213 264 307 342 369 388 389 390 391 376 353 322 283 236 181 118 47
12 13 88 155 214 265 308 343 370 371 372 373 374 375 352 321 282 235 180 117 46
13 14 89 156 215 266 309 344 345 346 347 348 349 350 351 320 281 234 179 116 45
14 15 90 157 216 267 310 311 312 313 314 315 316 317 318 319 280 233 178 115 44
15 16 91 158 217 268 269 270 271 272 273 274 275 276 277 278 279 232 177 114 43
16 17 92 159 218 219 220 221 222 223 224 225 226 227 228 229 230 231 176 113 42
17 18 93 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 112 41
18 19 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 40
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
*/
--回字形方阵的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
goexec sp_Test 6400drop procedure sp_Test
/*
1 116 115 114 113 112 111 110 109 108 107 106 105 104 103 102 101 100 99 98 97 96 95 94 93 92 91 90 89 88
2 117 224 223 222 221 220 219 218 217 216 215 214 213 212 211 210 209 208 207 206 205 204 203 202 201 200 199 198 87
3 118 225 324 323 322 321 320 319 318 317 316 315 314 313 312 311 310 309 308 307 306 305 304 303 302 301 300 197 86
4 119 226 325 416 415 414 413 412 411 410 409 408 407 406 405 404 403 402 401 400 399 398 397 396 395 394 299 196 85
5 120 227 326 417 500 499 498 497 496 495 494 493 492 491 490 489 488 487 486 485 484 483 482 481 480 393 298 195 84
6 121 228 327 418 501 576 575 574 573 572 571 570 569 568 567 566 565 564 563 562 561 560 559 558 479 392 297 194 83
7 122 229 328 419 502 577 644 643 642 641 640 639 638 637 636 635 634 633 632 631 630 629 628 557 478 391 296 193 82
8 123 230 329 420 503 578 645 704 703 702 701 700 699 698 697 696 695 694 693 692 691 690 627 556 477 390 295 192 81
9 124 231 330 421 504 579 646 705 756 755 754 753 752 751 750 749 748 747 746 745 744 689 626 555 476 389 294 191 80
10 125 232 331 422 505 580 647 706 757 800 799 798 797 796 795 794 793 792 791 790 743 688 625 554 475 388 293 190 79
11 126 233 332 423 506 581 648 707 758 801 836 835 834 833 832 831 830 829 828 789 742 687 624 553 474 387 292 189 78
12 127 234 333 424 507 582 649 708 759 802 837 864 863 862 861 860 859 858 827 788 741 686 623 552 473 386 291 188 77
13 128 235 334 425 508 583 650 709 760 803 838 865 884 883 882 881 880 857 826 787 740 685 622 551 472 385 290 187 76
14 129 236 335 426 509 584 651 710 761 804 839 866 885 896 895 894 879 856 825 786 739 684 621 550 471 384 289 186 75
15 130 237 336 427 510 585 652 711 762 805 840 867 886 897 900 893 878 855 824 785 738 683 620 549 470 383 288 185 74
16 131 238 337 428 511 586 653 712 763 806 841 868 887 898 899 892 877 854 823 784 737 682 619 548 469 382 287 184 73
17 132 239 338 429 512 587 654 713 764 807 842 869 888 889 890 891 876 853 822 783 736 681 618 547 468 381 286 183 72
18 133 240 339 430 513 588 655 714 765 808 843 870 871 872 873 874 875 852 821 782 735 680 617 546 467 380 285 182 71
19 134 241 340 431 514 589 656 715 766 809 844 845 846 847 848 849 850 851 820 781 734 679 616 545 466 379 284 181 70
20 135 242 341 432 515 590 657 716 767 810 811 812 813 814 815 816 817 818 819 780 733 678 615 544 465 378 283 180 69
21 136 243 342 433 516 591 658 717 768 769 770 771 772 773 774 775 776 777 778 779 732 677 614 543 464 377 282 179 68
22 137 244 343 434 517 592 659 718 719 720 721 722 723 724 725 726 727 728 729 730 731 676 613 542 463 376 281 178 67
23 138 245 344 435 518 593 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 612 541 462 375 280 177 66
24 139 246 345 436 519 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 540 461 374 279 176 65
25 140 247 346 437 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 460 373 278 175 64
26 141 248 347 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 372 277 174 63
27 142 249 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 276 173 62
28 143 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 172 61
29 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 60
30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
*/
用两个变量(@rf 行方向,@cf 列方向)就可以表示方向了,不需要为每个方向都编写计算代码,太累了。
表中的每个位置的数字都可以由函数算出来
设总数为n^2,位置r,c 的数值为 Y
则存在 Y = f(n,r,c)。
关键是总结函数F,总结的方法也很简单,看着数字方块逐个去试。
就是太耗时了,有兴趣的朋友可以试试,试出来告诉我。
递归的思想也非常简单。
设n为数字方正的行数,也是列数。
1、置起始编号start = 1。
2、求出方正最外圈。
3、求出最外全最大的数字 max。
4、置起始编号为start = max。
5、求n-2 的方正。
6、if n=1 ,这个大家都会。
递归的思想也非常简单。
设n为数字方正的行数,也是列数。
1、置起始编号start = 1。
2、求出方正最外圈。
3、求出最外圈最大的数字 max。
4、置起始编号为start = max+1。
5、求n-2 的方正。
6、if n=1 or n= 2 ,这个大家都会。
/*
@n/@a do熊 jht
-------------------------------------
6400/80 12s 16s
1600/40 0s 3s
400/20 0s 0s
*/
用两个变量(@rf 行方向,@cf 列方向)就可以表示方向了,不需要为每个方向都编写计算代码,太累了。--------
四个方向单独编码,是为了方便优化,,,
帮我测试一下下面的性能:
create function dbo.getfz(@n int,@c int,@h int,@w int)
returns varchar(8000)
as
begin
declare @s varchar(8000),@k int,@t varchar(8000)
select @t = rtrim(@c+@h),@k = 0
set @s = space(@w-len(@t))+@t
if @n = 1 return @s
if @h = 1 or @h = @n
begin
while @k<@n-1
begin
if @h = 1 set @t = rtrim(@c+4*@n-4-@k)
if @h = @n set @t = rtrim(@c+@n+@k+1)
select @s = @s+space(@w-len(@t))+ @t,@k=@k+1
end
end
else
begin
select @t = rtrim(@c + 3*@n-1-@h)
set @s = @s+dbo.getfz(@n-2,@c+4*@n-4,@h-1,@w)+space(@w-len(@t))+@t
end
return @s
end
go
declare @n int,@i int,@w int
select @i = 1,@n=20
set @w = len(power(@n,2))+1
while @i <=@n
begin
print dbo.getfz(@n,0,@i,@w)
set @i = @i +1
end
drop function dbo.getfz
/*
1 76 75 74 73 72 71 70 69 68 67 66 65 64 63 62 61 60 59 58
2 77 144 143 142 141 140 139 138 137 136 135 134 133 132 131 130 129 128 57
3 78 145 204 203 202 201 200 199 198 197 196 195 194 193 192 191 190 127 56
4 79 146 205 256 255 254 253 252 251 250 249 248 247 246 245 244 189 126 55
5 80 147 206 257 300 299 298 297 296 295 294 293 292 291 290 243 188 125 54
6 81 148 207 258 301 336 335 334 333 332 331 330 329 328 289 242 187 124 53
7 82 149 208 259 302 337 364 363 362 361 360 359 358 327 288 241 186 123 52
8 83 150 209 260 303 338 365 384 383 382 381 380 357 326 287 240 185 122 51
9 84 151 210 261 304 339 366 385 396 395 394 379 356 325 286 239 184 121 50
10 85 152 211 262 305 340 367 386 397 400 393 378 355 324 285 238 183 120 49
11 86 153 212 263 306 341 368 387 398 399 392 377 354 323 284 237 182 119 48
12 87 154 213 264 307 342 369 388 389 390 391 376 353 322 283 236 181 118 47
13 88 155 214 265 308 343 370 371 372 373 374 375 352 321 282 235 180 117 46
14 89 156 215 266 309 344 345 346 347 348 349 350 351 320 281 234 179 116 45
15 90 157 216 267 310 311 312 313 314 315 316 317 318 319 280 233 178 115 44
16 91 158 217 268 269 270 271 272 273 274 275 276 277 278 279 232 177 114 43
17 92 159 218 219 220 221 222 223 224 225 226 227 228 229 230 231 176 113 42
18 93 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 112 41
19 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 40
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
*/
不过依然受VARCHAR(8000)限制,@n最大为1000,用我的破机器为25S
create function dbo.getfzh(@n int,@h int)
returns varchar(8000)
as
begin
declare @c int,@w int,@k int,@t varchar(8000)
declare @s1 varchar(8000),@s2 varchar(8000),@s varchar(8000)
select @s1 = '',@s2='',@c = 0,@w = len(power(@n,2))+1
while @n<>@h and @h <> 1
begin
select @t = rtrim(@c+@h)
select @s1 = @s1+ space(@w-len(@t))+@t
select @t = rtrim(@c+3*@n-1-@h)
select @s2 = space(@w-len(@t))+@t+@s2,@c = @c+4*@n-4
select @n = @n - 2,@h = @h -1
end
select @t = rtrim(@c+@h),@k = 0
set @s = space(@w-len(@t))+@t
while @k<@n-1
begin
if @h = 1 set @t = rtrim(@c+4*@n-4-@k)
if @h = @n set @t = rtrim(@c+@n+@k+1)
select @s = @s+space(@w-len(@t))+@t,@k = @k +1
end
return @s1+@s+@s2
end
godeclare @n int,@i int
select @i = 1,@n = 1000
while @i <=@n
begin
print dbo.getfzh(@n,@i)
set @i = @i +1
end
drop function dbo.getfzh
不过依然受VARCHAR(8000)限制,@n最大为1000,用我的破机器为25S -------------------
在我的破机器上测了两次:3806ms, 3910ms
你的机器牛呀,是我机器性能的6倍。
不过依然受VARCHAR(8000)限制,@n最大为1000,用我的破机器为25S -------------------
在我的破机器上测了两次:3806ms, 3910ms
--------------------------------------------------
我电脑看来该扔了,将的算法我这里40s
汗..
-- 数字螺旋方阵的SQL函数
-- 作者: Limpire(昨夜小楼)
-- 日期: 2008-02-25
--================================================--
CREATE FUNCTION Matrix(@n int,@x int,@y int,@i int,@a bit,@c bit)
-- @n: 方阵的阶.
-- (@x,@y): 垂直倒置第1象限坐标, 由0开始.
-- @i: 初始值. 为null时: 如果是递增方式, 由1开始递增; 如果是递减方式, 由@n的平方开始递减.
-- @a: 1为递增方式, 0为递减方式.
-- @c: 1为顺时针方向, 0为逆时针方向
RETURNS int
ASBEGINdeclare @Layer int,@Return intif @x>@y set @Layer=@y
else set @Layer=@x
if @Layer>@n-@x-1 set @Layer=@n-@x-1
if @Layer>@n-@y-1 set @Layer=@n-@y-1if @a=1 -- 递增
begin
if @i is null set @i=1
if (@c=0 and @x<=@y) or (@c=1 and @x>=@y)
set @Return=@x+@y-2*@Layer+(@Layer*@n-power(@Layer,2))*4+@i
else
set @Return=4*@n-6*@Layer-@x-@y+(@Layer*@n-power(@Layer,2))*4+@i-4
endelse -- 递减
begin
if @i is null set @i=power(@n,2)
if (@c=0 and @x<=@y) or (@c=1 and @x>=@y)
set @Return=2*@Layer-@x-@y-(@Layer*@n-power(@Layer,2))*4+@i
else
set @Return=power(@n-2*@Layer-2,2)+@x+@y-2*@Layer-power(@n,2)+@i
endreturn (@Return)END
GO
set @n=5 -- 方阵阶
set @i=null -- 无初始
set @a=1 -- 递增方式
set @c=0 -- 逆时针
set @y=0
while @y<@n
begin
select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select'
while @x<@n
begin
if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
if @y=0 set @SQL=@SQL+'C'+ltrim(@x)
set @x=@x+1
end
set @y=@y+1
end
exec (@SQL)
/*
C0 C1 C2 C3 C4
----------- ----------- ----------- ----------- -----------
1 16 15 14 13
2 17 24 23 12
3 18 25 22 11
4 19 20 21 10
5 6 7 8 9
*/
set @n=5 -- 方阵阶
set @i=null -- 无初始
set @a=0 -- 递减
set @c=0 -- 逆时针
set @y=0
while @y<@n
begin
select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select'
while @x<@n
begin
if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
if @y=0 set @SQL=@SQL+'C'+ltrim(@x)
set @x=@x+1
end
set @y=@y+1
end
exec (@SQL)
/*
C0 C1 C2 C3 C4
----------- ----------- ----------- ----------- -----------
25 10 11 12 13
24 9 2 3 14
23 8 1 4 15
22 7 6 5 16
21 20 19 18 17
*/
set @n=5 -- 方阵阶
set @i=150 -- 初始150
set @a=1 -- 递增
set @c=1 -- 顺时针
set @y=0
while @y<@n
begin
select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select'
while @x<@n
begin
if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
if @y=0 set @SQL=@SQL+'C'+ltrim(@x)
set @x=@x+1
end
set @y=@y+1
end
exec (@SQL)
/*
C0 C1 C2 C3 C4
----------- ----------- ----------- ----------- -----------
150 151 152 153 154
165 166 167 168 155
164 173 174 169 156
163 172 171 170 157
162 161 160 159 158
*/
set @n=5 -- 方阵阶5
set @i=150 -- 初始150
set @a=0 -- 递减
set @c=1 -- 顺时针
set @y=0
while @y<@n
begin
select @x=0, @SQL=isnull(@SQL+' union all'+char(10),'')+'select'
while @x<@n
begin
if @x=0 set @SQL=@SQL+' dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
else set @SQL=@SQL+',dbo.Matrix('+ltrim(@n)+','+ltrim(@x)+','+ltrim(@y)+','+isnull(ltrim(@i),'null')+','+ltrim(@a)+','+ltrim(@c)+')'
if @y=0 set @SQL=@SQL+'C'+ltrim(@x)
set @x=@x+1
end
set @y=@y+1
end
exec (@SQL)
/*
C0 C1 C2 C3 C4
----------- ----------- ----------- ----------- -----------
150 149 148 147 146
135 134 133 132 145
136 127 126 131 144
137 128 129 130 143
138 139 140 141 142
*/
set @x = 9;with tmp1 as
(select top(@x) Row_Number() over(order by id) as x from sysobjects order by id)
select (
select convert(char(5),no) from (select x,y,row_number() over(order by c) as no
from (
select x,y, case c
when x-1 then (x-1)*100+ 0+y
when @x-y then (@x-y)*100+10+x
when @x-x then (@x-x)*100+20+(@x-y)
when y-1 then (y-1)*100+30+(@x-x)
end as c
from (
select x,y,case when c5<=c6 then c5 else c6 end as c
from (
select x,y,case when c1<=c2 then c1 else c2 end as c5,
case when c3<=c4 then c3 else c4 end as c6
from (
select x,y,x-1 as c1,@x-y as c2,@x-x as c3,y-1 as c4
from(
select a.x,b.x as y from tmp1 a,tmp1 b
) c
) d
) e
) f
) g
) h
where h.y=z.x
order by h.x
for xml path('')
) from tmp1 z
不过还有一点瑕疵:如果输入的不是完全平方数,就不能正确得出结果,
所以可以改一下输入为 行数(列数)--================================================
--绕圈的SQL算法(感觉很笨,不知道有没有更好的算法)
--作者:冷箫轻笛
--日期:2008-02-21
--执行方式(参数必须为整数的平方,但大小并不是无限制的)
--exec raoquan 2500
--修改:山之魂
--日期:2008-03-05
--执行方式(参数改为行数,可以输1-79的自然数,大了会出错,)
--exec raoquan 79 --机子慢的可能要很久
--================================================create alter proc raoquan
(
@i int
)
as
beginset nocount ondeclare @num int
set @num=@i*@iif CEILING(@i) <> @i
returndeclare @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
endselect @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 = 1declare @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
declare @x int, @y int;
set @x = 9;
set @y = 5;with tmp1 as
(select top(case when @x>@y then @x else @y end)
Row_Number() over(order by id) as x
from sysobjects
order by id)
select top(@y) (
select convert(char(5),no) from (select x,y,row_number() over(order by c) as no
from (
select x,y, case c
when x-1 then (x-1)*100+ 0+y
when @y-y then (@y-y)*100+10+x
when @x-x then (@x-x)*100+20+(@y-y)
when y-1 then (y-1)*100+30+(@x-x)
end as c
from (
select x,y,case when c5 <=c6 then c5 else c6 end as c
from (
select x,y,case when c1 <=c2 then c1 else c2 end as c5,
case when c3 <=c4 then c3 else c4 end as c6
from (
select x,y,x-1 as c1,@y-y as c2,@x-x as c3,y-1 as c4
from(
select a.x,b.x as y
from tmp1 a,tmp1 b
where a.x<=@x and b.x<=@y
) c
) d
) e
) f
) g
) h
where h.y=z.x
order by h.x
for xml path('')
) from tmp1 z /*-----------------------------------------
1 24 23 22 21 20 19 18 17
2 25 40 39 38 37 36 35 16
3 26 41 42 43 44 45 34 15
4 27 28 29 30 31 32 33 14
5 6 7 8 9 10 11 12 13 (5 行受影响)
*/
declare @x int, @y int;
set @x = 9;
set @y = 5;with tmp1 as
(select top(case when @x>@y then @x else @y end)
Row_Number() over(order by id) as x
from sysobjects
order by id)
select top(@y) (
select convert(char(5),no) from (select x,y,row_number() over(order by c) as no
from (
select x,y, case c
when x-1 then (x-1)*100+ 0+y
when @y-y then (@y-y)*100+10+x
when @x-x then (@x-x)*100+20+(@y-y)
when y-1 then (y-1)*100+30+(@x-x)
end as c
from (
select x,y,case when c5 <=c6 then c5 else c6 end as c
from (
select x,y,case when c1 <=c2 then c1 else c2 end as c5,
case when c3 <=c4 then c3 else c4 end as c6
from (
select x,y,x-1 as c1,@y-y as c2,@x-x as c3,y-1 as c4
from(
select a.x,b.x as y
from tmp1 a,tmp1 b
where a.x<=@x and b.x<=@y
) c
) d
) e
) f
) g
) h
where h.y=z.x
order by h.x
for xml path('')
) from tmp1 z
set @x>=13 的数试试
同样试试这样:
set @x >= 2
set @y >= 13
设为n*n
整个数组的层数为
ceiling(n/2)
declare @n int,@k int
select @n=5,@k=3
select ceiling(@n/2.0)
--算出数组元素x,y是属于哪一层
--第k层长度为 4*((n-2(k-1))-1)=4*(n-2k+1)
--第k层应减去的长度为 4*(n-2*1+1)+4*(n-2*2+1)+4*(n-2(k-1)+1)=4*((k-1)*(n+1)-2*(1+2+...k-1))
-- =4*((k-1)*(n+1)-k*(k-1))
-- =4*(k-1)*(n+1-k)=4*(k-1)*(n-k+1)
select 4*(@k-1)*(@n-@k+1)--明天再续
declare @n int,@k int,@maxlayer int,@i int,@j int,@il int,@jl int
declare @lstr varchar(8000),@lstr1 char(10)
declare @tmp table (lstr varchar(8000))
declare @tmp1 table (lstr varchar(8000))
select @n=5
select @maxlayer=ceiling(@n/2.0)
--算出数组元素i,j是属于哪一层
--第k层长度为 4*((n-2(k-1))-1)=4*(n-2k+1)
--第k层应减去的长度为 4*(n-2*1+1)+4*(n-2*2+1)+4*(n-2(k-1)+1)=4*((k-1)*(n+1)-2*(1+2+...k-1))
-- =4*((k-1)*(n+1)-k*(k-1))
-- =4*(k-1)*(n+1-k)=4*(k-1)*(n-k+1)
--select 4*(@k-1)*(@n-@k+1)
--第k层的每边长度n-2k+2
--@i的层数
set nocount on
select @i=1,@j=1
while @i<=@n
begin
--要求规则
set @lstr=''
while @j<=@n
begin
select @il=case when @i<=@maxlayer then @i else @n-@i+1 end,@jl=case when @j<=@maxlayer then @j else @n-@j+1 end
if @il>@jl set @k=@jl else set @k=@il
select @lstr1=convert(char(10),case when @j=@k then @i-@k+1+4*(@k-1)*(@n-@k+1) --left
when @n-@i+1=@k then 4*(@k-1)*(@n-@k+1)+@n-2*@k+2+@j-@k --bottom
when @n-@j+1=@k then 4*@k*(@n-@k)-(@n-2*@k+1)-@i+@k+1 --right
else 4*@k*(@n-@k)-@j+@k+1 --top
end)
if @lstr='' set @lstr=@lstr1 else set @lstr=@lstr+','+@lstr1
set @j=@j+1
end
insert into @tmp values(@lstr)
--改变一下规则,顺时针
set @j=1
set @lstr=''
while @j<=@n
begin
select @il=case when @i<=@maxlayer then @i else @n-@i+1 end,@jl=case when @j<=@maxlayer then @j else @n-@j+1 end
if @il>@jl set @k=@jl else set @k=@il
select @lstr1=convert(char(10),case when @i=@k then 4*(@k-1)*(@n-@k+1)+@j-@k+1 --top
when @n-@j+1=@k then 4*(@k-1)*(@n-@k+1)+@n-2*@k+2+@i-@k --right
when @n-@i+1=@k then 4*@k*(@n-@k)-(@n-2*@k+1)-@j+@k+1 --bottom
when @j=@k then 4*@k*(@n-@k)-@i+@k+1 --left
end)
if @lstr='' set @lstr=@lstr1 else set @lstr=@lstr+','+@lstr1
set @j=@j+1
end
insert into @tmp1 values(@lstr)
select @i=@i+1,@j=1
end
select * from @tmp
select * from @tmp1
当行列数太多的时候,可以适当增加when @x-y then (@x-y)*100+10+x附近那几行的常量的值,
例如改成when @y-y then (@y-y)*10000+1000+x,
当然,要一劳永逸的话,把这一段分开三列再排序就可以了,懒得去改啦,有兴趣的朋友自己试吧。
循环来实现这没意思啦,所以我只用了一个SQL语句来完成。
效率还是挺高的,上面一位用循环的朋友说一万个数要一分钟,
我这里只花了八秒,再优化一下的话还能提高效率的。
ALTER PROCEDURE dbo.test3
@Count int
as
declare @T Table (id int,x int ,y int)
Declare @i int
set @i =power(@count,0.5)
declare @j int--填充数量
set @j=1
declare @Direction int--填充方向
set @Direction=1
declare @x int--横坐标值
declare @y int--纵坐标值
set @x=1
set @y=1
declare @index int--不同方向填充的个数
set @index =1
while @j<=@count
begin
insert @t select @j,@x,@y
set @j=@j+1
if @index =@i--该方向填充满时,填充方向改变
begin
set @Direction=@Direction % 4 +1
if @Direction=2 or @Direction=4
set @i=@i-1--当填充方向为横向时,该方向填充个数减1
set @Index =0
end
if @Direction=1
set @y=@y+1--从上往下填充时,Y坐标递增,X坐标不变
if @Direction=2
set @X=@X+1--从左住右填充时,X坐标递增,Y坐标不变
if @Direction=3
set @y=@Y-1--从上往下填充时,Y坐标递减,X坐标不变
if @Direction=4
set @x=@X-1--从左住右填充时,X坐标递减,Y坐标不变
set @index =@index+1
end
set @y=1
declare @str varchar(2000)
Declare @TT Table (Re varchar(2000))
while @j <=power(@count,0.5)
begin
set @Str=''
select @str=@str +left(cast(id as varchar(10))+' ',6) from @t where y=@y order by x
insert @TT select @str as re
set @y=@y+1
end
select * from @TT
幸亏我的机器比你的牛X,我的代码经过改写后算1000000(相当于@n=1000)也只需要6s了,比你的25s强多了,呵呵~!~!
declare @r int,@c int,@rf int,@cf int
select @a=20,@k = 0
create table # (id int)
while @k < @a
begin
insert #(id) select @k
set @s = 'alter table # add c'+rtrim(@k)+' int' exec(@s)
set @k = @k +1
end
select @r =-1,@c = 0,@rf = 1,@cf = 0,@i = 0
while @a > 0
begin
set @k = 0
while @k <@a
begin
select @k = @k +1,@i= @i+1,@r = @r+@rf,@c = @c +@cf
set @s = 'update # set c'+rtrim(@c)+'= '+rtrim(@i)+' where id= '+rtrim(@r) exec(@s)
print @s
end
if @rf = 1 and @cf = 0 select @rf = 0,@cf = 1
else if @rf = 0 and @cf = 1 select @rf = -1,@cf = 0
else if @rf = -1 and @cf = 0 select @rf = 0,@cf = -1
else if @rf = 0 and @cf = -1 select @rf = 1,@cf = 0
if @rf = 0 set @a = @a-1
end
select * from #
drop table #