求一sql语句

[dralion]

[dralion]

3楼效率最高，谢谢

[骑着蜗牛去爬山]

with tb(id,num) as(
select 1,'5,8,11' union all
select 2,'6' union all
select 3,'1,98,44' union all
select 4,'5,12,23'
),cte as(select t.*,cast(substring(','+num+',',number+1,charindex(',',','+num+',',number+1)-number-1) as decimal(10,0))+10 as _num from tb t,master..spt_values where type='P' and number between 1 and len(num)+1 and substring(','+num+',',number,1)=',')
select id,max(num) num,stuff((select ','+rtrim(_num) from cte c2 where c2.id=c1.id for xml path('')),1,1,'') _num from cte c1 group by id

[二月十六]

创建分隔函数：

CREATE FUNCTION dbo.f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+'a')-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
 END
 INSERT @re VALUES(@s)
 RETURN
END

GO

语句：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[num] nvarchar(27))
Insert #T
select 1,N'5,8,11' union all
select 2,N'6' union all
select 3,N'1,98,44' union all
select 4,N'5,12,23'
Go
--测试数据结束
;WITH temp AS (
SELECT  id ,
       ( t.col + 10) AS newnum
FROM    #T
        CROSS APPLY ( SELECT    col
                      FROM      dbo.f_splitSTR([num], ',')
                    ) t
)
SELECT  a.id ,
        STUFF(( SELECT  ',' + RTRIM(newnum)
                FROM    temp b
                WHERE   a.id = b.id
              FOR
                XML PATH('')
              ), 1, 1, '') AS num
FROM    #T a

结果：

[唐诗三百首]

create table #t(Id int,num varchar(50))

insert into #t(Id,num)
 select 1,'5,8,11' union all
 select 2,'6' union all
 select 3,'1,98,44' union all
 select 4,'5,12,23'


update a
 set a.num=stuff((select ','+rtrim(S)
                   from (select S=o.value('.','int')+10
                         from (select S=convert(xml,'<r><n>'+replace(a.num,',','</n><n>')+'</n></r>')) t
                         cross apply S.nodes('/r/n') x(o)) u
                   for xml path('')),1,1,'')
 from #t a


-- 结果
select * from #t

/*
Id          num
----------- -----------------------
1           15,18,21
2           16
3           11,108,54
4           15,22,33

(4 row(s) affected)
*/