有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) + ') + 1declare @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
*/
--================================================
--绕圈的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
*/
