declare @t table(id char(3),num int) declare @i int set @I = 0 while (@i < 100) begin insert into @t(num) select rand()*100 set @i = @i +1 endset @I = 0update @t set id = char(65+@i/9)+right('00'+ltrim(@I%9),2),@i = @i + case when @i %9 = 8 then 2 else 1 end select * from @t/* id num ---- ----------- A01 21 A02 36 A03 18 A04 23 A05 22 A06 15 A07 19 A08 18 B01 70 B02 89 B03 10 B04 85 B05 65 B06 75 B07 26 B08 62 C01 54 C02 22 C03 41 C04 39 C05 46...*/
--> 测试数据: 表名 create table 表名(num int) insert into 表名 select 33 union all select 12 union all select 2 union all select 234 union all select 25 union all select 56 union all select 34 union all select 4 union all select 345 union all select 5 union all select 667 union all select 65 union all select 56 union all select 34 union all select 4 union all select 345 union all select 5 union all select 667 union all select 65select id=identity(int,1,1),* into # from 表名 select 编号=char(65+(case id%8 when 0 then id/8-1 else id/8 end))+ltrim(case id%8 when 0 then 8 else id%8 end),* from #
--插入80条记录INSERT @tb
SELECT TOP(80) ABS(CHECKSUM(NEWID())) % 100 + 10 FROM sysobjects,syscolumns;WITH Liang AS
(
SELECT rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()),* FROM @tb
)
SELECT CHAR(65 + (rowid-1)/8)+RIGHT(100+(rowid-1)%8+1,2) AS [No],number FROM Liang
----- -----------
A01 44
A02 52
A03 101
A04 41
A05 59
A06 89
A07 64
A08 51
B01 93
B02 98
B03 107
B04 58
B05 61
B06 49
B07 55
B08 71
C01 74
C02 14
C03 105
C04 51
C05 64
C06 97
C07 54
C08 14
D01 31
D02 98
D03 61
D04 103
D05 44
D06 33
D07 62
D08 69
E01 74
E02 17
E03 12
E04 62
E05 79
E06 58
E07 87
E08 88
F01 22
F02 25
F03 72
F04 53
F05 58
F06 81
F07 19
F08 59
G01 19
G02 41
G03 14
G04 40
G05 46
G06 16
G07 30
G08 89
H01 79
H02 71
H03 44
H04 54
H05 30
H06 39
H07 21
H08 106
I01 16
I02 94
I03 93
I04 99
I05 21
I06 109
I07 10
I08 102
J01 59
J02 104
J03 93
J04 64
J05 54
J06 63
J07 39
J08 41(80 行受影响)
declare @i int
set @I = 0
while (@i < 100)
begin
insert into @t(num) select rand()*100
set @i = @i +1
endset @I = 0update @t
set id = char(65+@i/10)+right('00'+ltrim(@I%9),2),@i = @i + case when @i %10 = 8 then 2 else 1 end select * from @t
id num
---- -----------
A01 71
A02 27
A03 42
A04 63
A05 77
A06 74
A07 88
A08 88
B01 99
B02 4
B03 1
B04 85
B05 99
B06 10
B07 43
B08 24
B00 36
C02 5
C03 30
C04 4
C05 75
C06 17
C07 70
C08 19
C00 13
C01 28
D03 26
D04 63
D05 35
D06 80
D07 10
D08 39
D00 71
D01 24
D02 46
E04 86
E05 51
E06 30
E07 84
E08 71
E00 99
E01 86
E02 76
E03 84
F05 73
F06 78
F07 15
F08 11
F00 64
F01 25
F02 68
F03 25
F04 68
G06 75
G07 16
G08 19
G00 19
G01 14
G02 20
G03 86
G04 10
G05 20
H07 13
H08 32
H00 0
H01 13
H02 70
H03 14
H04 32
H05 42
H06 10
I08 62
I00 48
I01 41
I02 6
I03 97
I04 93
I05 69
I06 96
I07 17
J00 19
J01 70
J02 26
J03 26
J04 85
J05 42
J06 98
J07 20
J08 85
K01 41
K02 68
K03 3
K04 51
K05 8
K06 81
K07 51
K08 14
K00 66
L02 95
L03 53(100 行受影响)
declare @i int
set @I = 0
while (@i < 100)
begin
insert into @t(num) select rand()*100
set @i = @i +1
endset @I = 0update @t
set id = char(65+@i/9)+right('00'+ltrim(@I%9),2),@i = @i + case when @i %9 = 8 then 2 else 1 end select * from @t/*
id num
---- -----------
A01 21
A02 36
A03 18
A04 23
A05 22
A06 15
A07 19
A08 18
B01 70
B02 89
B03 10
B04 85
B05 65
B06 75
B07 26
B08 62
C01 54
C02 22
C03 41
C04 39
C05 46...*/
create table 表名(num int)
insert into 表名
select 33 union all
select 12 union all
select 2 union all
select 234 union all
select 25 union all
select 56 union all
select 34 union all
select 4 union all
select 345 union all
select 5 union all
select 667 union all
select 65 union all
select 56 union all
select 34 union all
select 4 union all
select 345 union all
select 5 union all
select 667 union all
select 65select id=identity(int,1,1),* into # from 表名
select 编号=char(65+(case id%8 when 0 then id/8-1 else id/8 end))+ltrim(case id%8 when 0 then 8 else id%8 end),* from #