求解一个字符串的循环累加有关问题，在线

求解一个字符串的循环累加问题，在线
declare @xm varchar(50)
select @xm='XM001'
求解，怎么给这个循环累加，例如XM002，XM003这样，在线急求。。。

[解决办法]

SQL code

declare @s as varchar(1000)set @s = ''declare @i as intset @i = 1while @i <= 10begin  set @s = @s + 'XM' + right('00' + cast(@i as varchar),3) + ','  set @i = @i + 1endset @s = substring(@s , 1 , len(@s) - 1)print @s/*XM001,XM002,XM003,XM004,XM005,XM006,XM007,XM008,XM009,XM010*/
[解决办法]
SQL code
declare @xm varchar(50)select @xm='XM'select top 1000 id=identity(int,1,1) into #tempfrom sysobjects,syscolumnsselect @xm+right(1000+id,3) as idfrom #tempdrop table #temp
[解决办法]
SQL code
select *,rn='XM'+right(1000+row_number() over (order by getdate()),3)from(select dept_code,title,dept_sid from view_hum_dept where full_name like '%sanan%'union allselect @deptcode,drbm,drbm_sid=@deptsid from sa_ygddtzd)t