SqlServer Stuff
时间：2021-07-01 10:21:17
帮助过：3人阅读
DECLARE @TAB TABLE
(
UserID INT,
UserName NVARCHAR(
50)
)
INSERT INTO @TAB
(
UserID,
UserName
)
VALUES
(0,N
‘西门炊鳕‘),
(1,N
‘夜雇乘‘),
(2,N
‘令狐冲‘),
(3,N
‘士兵甲‘),
(4,N
‘胡学‘),
(5,N
‘五道口‘),
(6,N
‘六道口‘)
--目的.将用户名用分隔符‘,‘隔开.最后得出字符:"西门炊鳕,夜雇乘,令狐冲,士兵甲,胡学,五道口,六道口"
--最终的Sql语句猛一看,有点懵,下面分解开来一步步说.
SELECT STUFF
(
(SELECT ‘,‘+UserName
FROM @TAB FOR XML PATH(
‘‘)),
1,
1,
‘‘
)
--SELECT *FROM @TAB
--FOR XML PATH(‘‘)
SELECT UserName
FROM @TAB FOR XML PATH(
‘‘)
--FOR XML PATH(‘‘)之加个分隔符‘,‘
SELECT ‘,‘+UserName
FROM @TAB FOR XML PATH(
‘‘)
--Stuff将第一个分隔符‘,‘截取
SELECT STUFF
(
(SELECT ‘,‘+UserName
FROM @TAB FOR XML PATH(
‘‘)),
1,
1,
‘‘
)
--Stuff将第一个分隔符‘***‘截取
SELECT STUFF
(
(SELECT ‘***‘+UserName
FROM @TAB FOR XML PATH(
‘‘)),
1,
3,
‘‘
)
SqlServer Stuff
标签：pat select bsp int var nbsp username sele div
