表 a2009
thekch列thekch生成一个数据如090100101,090100102.........
其中 第一二位 09表示时间,这个不变固定,第三四位 01 可变值有01,02,03.....10,第五六七位001可变值001,002....100,第八九位01可变值有01,02....30
thekch列thekch生成一个数据如090100101,090100102.........
其中 第一二位 09表示时间,这个不变固定,第三四位 01 可变值有01,02,03.....10,第五六七位001可变值001,002....100,第八九位01可变值有01,02....30
'09' +
right('00'+cast(id as varchar),2) +
right('000'+cast(id as varchar),2) +
right('00'+cast(id as varchar),2)
from
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 ) t/*
id
--------------
09010101
09020202
09030303
09040404
09050505(所影响的行数为 5 行)
*/
'09' +
right('00'+cast(id as varchar),2) +
right('000'+cast(id as varchar),3) +
right('00'+cast(id as varchar),2)
from
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 ) t/*
id
----------------
090100101
090200202
090300303
090400404
090500505(所影响的行数为 5 行)
*/
'09' +
right('00'+cast(id as varchar),2) +
right('000'+cast(id as varchar),3) +
right('00'+cast(id as varchar),2)
from
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 ) t/*
id
----------------
090100101
090200202
090300303
090400404
090500505(所影响的行数为 5 行)
*/select id = '09' + t1.id + t2.id + t3.id from
(select right('00'+cast(id as varchar),2) id from (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 ) t) t1,
(select right('000'+cast(id as varchar),3) id from (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 ) t) t2,
(select right('00'+cast(id as varchar),2) id from (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 ) t) t3/*
id
----------------
090100101
090100201
090100301
090100401
090100501
090100102
090100202
090100302
090100402
090100502
090100103
090100203
090100303
090100403
090100503
090100104
090100204
090100304
090100404
090100504
090100105
090100205
090100305
090100405
090100505
090200101
090200201
090200301
090200401
090200501
090200102
090200202
090200302
090200402
090200502
090200103
090200203
090200303
090200403
090200503
090200104
090200204
090200304
090200404
090200504
090200105
090200205
090200305
090200405
090200505
090300101
090300201
090300301
090300401
090300501
090300102
090300202
090300302
090300402
090300502
090300103
090300203
090300303
090300403
090300503
090300104
090300204
090300304
090300404
090300504
090300105
090300205
090300305
090300405
090300505
090400101
090400201
090400301
090400401
090400501
090400102
090400202
090400302
090400402
090400502
090400103
090400203
090400303
090400403
090400503
090400104
090400204
090400304
090400404
090400504
090400105
090400205
090400305
090400405
090400505
090500101
090500201
090500301
090500401
090500501
090500102
090500202
090500302
090500402
090500502
090500103
090500203
090500303
090500403
090500503
090500104
090500204
090500304
090500404
090500504
090500105
090500205
090500305
090500405
090500505(所影响的行数为 125 行)*/
SELECT * FROM a2009 a--result
/*a b c thekch
---- ---- ---- ---------
0901 001 03 090100103(所影响的行数为 1 行)*/设计成计算列,就不用拆分thekch了
我写了一个,但行不通
use cc
declare @i int,@j int,@k int
set @i=1 set @j=1 set @k=1
while @i<=5 while @j<=100 while @k<=30
begin
update a2009 set thekch='09'+ rtrim(ltrim(('0'+CONVERT(CHAR(2),@i))))+rtrim(ltrim(('00'+CONVERT(CHAR(3),@j))))+rtrim(ltrim(('0'+CONVERT(CHAR(2),@k))))
select @i=@i+1 , @j=@j+1 , @k=@k+1
end
declare @i int,@j int,@k int
set @i=1 set @j=1 set @k=1
while @i <=5 while @j <=100 while @k <=30
begin
update a2009 set thekch='09'+ rtrim(ltrim(('0'+CONVERT(CHAR(2),@i))))+rtrim(ltrim (('00'+CONVERT(CHAR(3),@j))))+rtrim(ltrim(('0'+CONVERT(CHAR(2),@k))))
select @i=@i+1 , @j=@j+1 , @k=@k+1
end
go
create table [a2009]([thekch] varchar(9))declare @i34 int,@i567 int,@i89 int
set @i34= 1
while @i34<=10
begin
set @i567=1
while @i567<=100
begin
set @i89=1
while @i89<=30
begin
insert into [a2009]([thekch]) select '09'+right('00'+ltrim(@i34),2)+right('000'+ltrim(@i567),3)+right('00'+ltrim(@i89),2)
set @i89=@i89+1
end
set @i567=@i567+1
end
set @i34=@i34+1
endselect * from [a2009] order by thekch--部分结果
/**
090100101
090100102
090100103
090100104
090100105
090100106
090100107
090100108
090100109
090100110
090100111
090100112
090100113
090100114
090100115
090100116
090100117
090100118
090100119
090100120
090100121
090100122
090100123
090100124
090100125
090100126
090100127
090100128
090100129
090100130
090100201
090100202
090100203
090100204
090100205
**/