求条SQL语句

[fancydong]

有这样的数据
item_cls item_subno
0101 0
0101 0
0101 0
0101 0
0101 0
0102 0
0102 0
0102 0
0102 0

。。。。。。。。。。。
-----------
求这样的更新语句，自动生成item_subno，生成的规则为item_cls加3位流水号
item_cls item_subno
0101 0101001
0101 0101002
0101 0101003
0101 0101004
0101 0101005
0102 0102001
0102 0102002
0102 0102003
0102 0102004
。。。。。。。。。

[水族杰纶]

if object_id('tempdb..#')is not null drop table #
create table # (item_cls varchar(10),item_subno varchar(10)) 
insert # select '0101'     ,       0 
insert # select '0101'     ,       0 
insert # select '0101'     ,       0 
insert # select '0101'     ,       0 
insert # select '0101'     ,       0 
insert # select '0102'     ,       0 
insert # select '0102'     ,       0 
insert # select '0102'     ,       0 
insert # select '0102'     ,       0 
alter table # 
add ID int identity(1,1)
go
update t set item_subno=item_cls+right('0000'+cast((select count(*)+1 from # where item_cls=t.item_cls and ID<t.id )as varchar(3)),3)
from # t
alter table #
drop column ID
go
select * from #
/*
item_cls   item_subno 
---------- ---------- 
0101       0101001
0101       0101002
0101       0101003
0101       0101004
0101       0101005
0102       0102001
0102       0102002
0102       0102003
0102       0102004


*/

[wxg22526451]

--> Test Data: @T
declare @T table ([item_cls] varchar(4),[item_subno] int)
insert into @T
select '0101',0 union all
select '0101',0 union all
select '0101',0 union all
select '0101',0 union all
select '0101',0 union all
select '0102',0 union all
select '0102',0 union all
select '0102',0 union all
select '0102',0

--select * from @T
--Code
--SQL2005
;with cte as
(
select [item_cls],[item_subno],px=ROW_NUMBER() over(partition by [item_cls] order by [item_cls]) from @T
)

update t set
		[item_subno]=[item_cls]+right('0000'+	ltrim(px),3) 
from cte t
		
select * from @T

--Result
/*
item_cls item_subno
-------- -----------
0101     101001
0101     101002
0101     101003
0101     101004
0101     101005
0102     102001
0102     102002
0102     102003
0102     102004
*/

[rucypli]

select item_cls ,cast(item_cls as varchar(10))+right('00' + cast（item_subno as varchar(10)),3) as item_subno
from table

[flyfly2008]

declare @id int
set @id =1
select item_cls+right('00',@id,3) as item_subno