Select ltrim([num])+right(1000+number,3)col from tb a,master..spt_values b where type='P' and number between 1 and 999
sql中的循环要用游标，先占个sf
if not object_id('tb') is null drop table tb Go Create table tb([num] int) Insert tb select 111 union all select 112 union all select 113 union all select 114 Go Select ltrim([num])+right(1000+number,3)col from tb a,master..spt_values b where type='P' and number between 1 and 999 /* col ------------------ 111001 112001 113001 114001 111002 112002 113002 114002 111003 112003 113003 114003 111004 112004 113004 114004 111005 112005 113005 114005 111006 112006 113006 114006 111007 112007 113007 114007 111008 112008 113008 114008 111009 112009 113009 114009 111010 112010 113010 114010 111011 112011 113011 114011 111012 112012 113012 114012 111013 112013 113013 114013 111014 112014 113014 114014 111015 112015 113015 114015 111016 112016 113016 114016 111017 112017 113017 114017 111018 112018 113018 114018 111019 112019 113019 114019 111020 112020 113020 114020 111021 112021 113021 114021 111022 */
一般循环 declare @i int set @i=1 while @i<1000 begin ... end表循环 declare @num varchar(5) declare cur cursor for select num from table1 open cur fetch next from cur into @num while @@fetch_status=0 begin .... fetch next from cur into @num end
select max(number) from master..spt_values where type='P' /* ----------- 2047 */
select top 999 id=identity(int,1,1) into # from sys.objects,sys.columns goselect rtrim(num)+right('0000'+ltrim(id),6) as num from table1,# go
if exists(select * from sysobjects where [name]='table1') drop table table1 go create table table1 ( number varchar(20) ) go insert into table1(number) ( select '111' union select '112' union select '113' union select '114' ) go if exists(select * from sysobjects where [name]='table2') drop table table2 go create table table2 ( number varchar(20) ) godeclare @number varchar(20) declare @i int declare cur cursor for select number from table1 order by number open cur Fetch next From Cur into @number while @@fetch_status=0 Begin set @i=1 while @i<=999 begin if @i<10 insert into table2(number) values(convert(varchar(3),@number)+'00'+convert(varchar(3),@i)) if @i>=10 and @i<100 insert into table2(number) values(convert(varchar(3),@number)+'0'+convert(varchar(3),@i)) if @i>=100 insert into table2(number) values(convert(varchar(3),@number)+convert(varchar(3),@i)) set @i = @i + 1 end Fetch Next From Cur Into @number endclose cur Deallocate Cur goselect * from table2 order by number desc go select count(*) from table2 go
Select ltrim([num])+right(1000+number,3)num1 into table2 from tb a,master..spt_values b where type='P' and number between 1 and 999
select top 9999 id=identity(int,1,1) into #aa from sysobjects,syscolumns goselect rtrim(num)+right('10000'+[id],4) as num into table from #table1,#aa go这种实现的。。谢谢各位
from tb a,master..spt_values b
where type='P' and number between 1 and 999
drop table tb
Go
Create table tb([num] int)
Insert tb
select 111 union all
select 112 union all
select 113 union all
select 114
Go
Select ltrim([num])+right(1000+number,3)col
from tb a,master..spt_values b
where type='P' and number between 1 and 999
/*
col
------------------
111001
112001
113001
114001
111002
112002
113002
114002
111003
112003
113003
114003
111004
112004
113004
114004
111005
112005
113005
114005
111006
112006
113006
114006
111007
112007
113007
114007
111008
112008
113008
114008
111009
112009
113009
114009
111010
112010
113010
114010
111011
112011
113011
114011
111012
112012
113012
114012
111013
112013
113013
114013
111014
112014
113014
114014
111015
112015
113015
114015
111016
112016
113016
114016
111017
112017
113017
114017
111018
112018
113018
114018
111019
112019
113019
114019
111020
112020
113020
114020
111021
112021
113021
114021
111022
*/
declare @i int
set @i=1
while @i<1000
begin
...
end表循环
declare @num varchar(5)
declare cur cursor for select num from table1
open cur
fetch next from cur into @num
while @@fetch_status=0
begin
....
fetch next from cur into @num
end
from master..spt_values
where type='P'
/*
-----------
2047
*/
goselect rtrim(num)+right('0000'+ltrim(id),6) as num from table1,#
go
select top 999 id=identity(int,1,1) into # from sys.objects,sys.columns
goselect rtrim(num)+right('000'+ltrim(id),3) as num from tb,#
go
/**
num
------------------
111001
111002
111003
111004
111005
111006
111007
111008
111009
111010
111011
111012
111013
111014
111015
111016
111017
111018
111019
111020
111021
111022
111023
111024
111025
111026
111027
111028
111029
111030
111031
111032
111033
111034
111035
111036
111037
111038
111039
111040
111041
111042
111043
111044
111045
111046
111047
111048
111049
111050
111051
111052
111053
111054
111055
111056
111057
111058
111059
111060
111061
111062
111063
111064
111065
111066
111067
111068
111069
111070
111071
111072
111073
111074
111075
111076
111077
111078
111079
111080
111081
111082
111083
111084
111085
111086
111087
111088
111089
111090
111091
111092
111093
111094
111095
111096
111097
111098
111099
111100
111101
111102
111103
111104
111105
111106
111107
111108
111109
111110
111111
111112
111113
111114
111115
111116
111117
111118
111119
111120
111121
111122
111123
111124
111125
111126
111127
111128
111129
111130
111131
111132
111133
111134
111135
111136
111137
111138
111139
111140
111141
111142
111143
111144
111145
111146
111147
111148
111149
111150
111151
111152
111153
111154
111155
111156
111157
111158
111159
111160
111161
111162
111163
111164
111165
111166
111167
111168
111169
111170
111171
111172
111173
111174
111175
111176
111177
111178
111179
111180
111181
111182
111183
111184
111185
111186
111187
111188
111189
111190
111191
111192
111193
111194
111195
111196
111197
111198
111199
111200
111201
111202
111203
111204
111205
111206
111207
111208
111209
111210
111211
111212
111213
111214
111215
111216
111217
111218
111219
111220
111221
111222
111223
111224
111225
111226
111227
111228
111229
111230
111231
111232
111233
111234
111235
111236
111237
111238
111239
111240
111241
111242
111243
111244
111245
111246
111247
111248
111249
111250
111251
111252
111253
111254
111255
111256
111257
111258
111259
111260
111261
111262
111263
111264
111265
111266
111267
111268
111269
111270
111271
111272
111273
111274
111275
111276
111277
111278
111279
111280
111281
111282
111283
111284
111285
111286
111287
111288
111289
111290
111291
111292
111293
111294
111295
111296
111297
111298
111299
111300
111301
111302
111303
111304
111305
111306
111307
111308
111309
111310
111311
111312
111313
111314
111315
111316
111317
111318
111319
111320
111321
111322
111323
111324
111325
111326
111327
111328
111329
111330
111331
111332
111333
111334
111335
111336
111337
111338
111339
111340
111341
111342
111343
111344
111345
111346
111347
111348
111349
111350
111351
111352
111353
111354
111355
111356
111357
111358
111359
111360
111361
111362
111363
111364
111365
111366
111367
111368
111369
111370
111371
111372
111373
111374
111375
111376
111377
111378
111379
111380
111381
111382
111383
111384
111385
111386
111387
111388
111389
111390
111391
111392
111393
111394
111395
111396
111397
111398
111399
111400
111401
111402
111403
111404
111405
111406
111407
111408
111409
111410
111411
111412
111413
111414
111415
111416
111417
111418
111419
111420
111421
111422
111423
111424
111425
111426
111427
111428
111429
111430
111431
111432
111433
111434
111435
111436
111437
111438
111439
111440
111441
111442
111443
111444
111445
111446
111447
111448
111449
111450
111451
111452
111453
111454
111455
111456
111457
111458
111459
111460
111461
111462
111463
111464
111465
111466
111467
111468
111469
111470
111471
111472
111473
111474
111475
111476
111477
111478
111479
111480
111481
111482
111483
111484
111485
111486
111487
111488
111489
111490
111491
111492
111493
111494
111495
111496
111497
111498
111499
111500
111501
111502
111503
111504
111505
111506
111507
111508
111509
111510
111511
111512
111513
111514
111515
111516
111517
111518
111519
111520
111521
111522
111523
111524
111525
111526
111527
111528
111529
111530
111531
111532
....
114075
114076
114077
...
114951
114952
114953
114954
114955
114956
114957
114958
114959
114960
114961
114962
114963
114964
114965
114966
114967
114968
114969
114970
114971
114972
114973
114974
114975
114976
114977
114978
114979
114980
114981
114982
114983
114984
114985
114986
114987
114988
114989
114990
114991
114992
114993
114994
114995
114996
114997
114998
114999(3996 行受影响)**/
drop table #,tb
drop table table1
go
create table table1
(
number varchar(20)
)
go
insert into table1(number)
(
select '111' union
select '112' union
select '113' union
select '114'
)
go
if exists(select * from sysobjects where [name]='table2')
drop table table2
go
create table table2
(
number varchar(20)
)
godeclare @number varchar(20)
declare @i int
declare cur cursor for
select number from table1 order by number
open cur
Fetch next From Cur into @number
while @@fetch_status=0
Begin
set @i=1
while @i<=999
begin
if @i<10
insert into table2(number) values(convert(varchar(3),@number)+'00'+convert(varchar(3),@i))
if @i>=10 and @i<100
insert into table2(number) values(convert(varchar(3),@number)+'0'+convert(varchar(3),@i))
if @i>=100
insert into table2(number) values(convert(varchar(3),@number)+convert(varchar(3),@i))
set @i = @i + 1
end
Fetch Next From Cur Into @number
endclose cur
Deallocate Cur
goselect * from table2 order by number desc
go
select count(*) from table2
go
into table2
from tb a,master..spt_values b
where type='P' and number between 1 and 999
select top 9999 id=identity(int,1,1) into #aa from sysobjects,syscolumns
goselect rtrim(num)+right('10000'+[id],4) as num into table from #table1,#aa
go这种实现的。。谢谢各位
SQL的强项是批量处理，循环是它的软肋。