关于分别更新表中字段内容的问题（MSSQL）
关于分别更新表中字段内容的问题（MSSQL）
一个表Userinfo的字段Selfid内容如下：
Id Selfid
----------------
1 1,11,30
2 1,55,64,31
3 1,719,721,32
4 2,708,715,34
想达到字段里每一个“，”分割的内容都加1000的效果，即：
Id Selfid
-------------------------
1 1001,1011,1030
2 1001,1055,1064,1031
3 1001,1719,1721,1032
4 1002,1708,1715,1034
求教实现的SQL，谢谢！！！！ sql
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-16 09:51:41
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[Userinfo]
if object_id('[Userinfo]') is not null drop table [Userinfo]
go
create table [Userinfo]([Id] int,[Selfid] varchar(12))
insert [Userinfo]
select 1,'1,11,30' union all
select 2,'1,55,64,31' union all
select 3,'1,719,721,32' union all
select 4,'2,708,715,34'
--------------开始查询--------------------------
;WITH cte AS
(
SELECT id ,
CONVERT(INT,SUBSTRING([Selfid], number, CHARINDEX(',', [Selfid] + ',', number) - number) )+1000
AS [Selfid]
FROM [Userinfo] a ,
master..spt_values
WHERE number >= 1
AND number < =LEN([Selfid])
AND type = 'p'
AND SUBSTRING(',' + [Selfid], number, 1) = ',')
SELECT a.id ,
STUFF(( SELECT ',' + CONVERT(VARCHAR(20),[Selfid])
FROM cte b
WHERE b.id = a.id
FOR
XML PATH('')
), 1, 1, '') '[Selfid]'
FROM cte a
GROUP BY a.id
----------------结果----------------------------
/*
id [Selfid]
----------- ----------------------------------------------------------------------------------------------------------------
1 1001,1011,1030
2 1001,1055,1064,1031
3 1001,1719,1721,1032
4 1002,1708,1715,1034
(4 行受影响)
*/
