在SQL中可使用逗号来将同组中多次出现的值合并在一起。

发布时间：2023-08-04 14:17:37 所属栏目：MsSql教程来源：

导读：一.定义表变量

<div class="codetitle"><a style="CURSOR: pointer" data="75855" class="copybut" id="copybut75855" onclick="doCopy('code75855')"> 代码如下:

<div class=&qu

一.定义表变量

<div class="codetitle"><a style="CURSOR: pointer" data="75855" class="copybut" id="copybut75855" onclick="doCopy('code75855')"> 代码如下:

<div class="codebody" id="code75855">

DECLARE @T1 table

(

UserID int,

UserName nvarchar(50),

CityName nvarchar(50)

);

<div class="codetitle"><a style="CURSOR: pointer" data="67569" class="copybut" id="copybut67569" onclick="doCopy('code67569')"> 代码如下:

<div class="codebody" id="code67569">

insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')

insert into @T1 (UserID,CityName) values (2,'b','北京')

insert into @T1 (UserID,CityName) values (3,'c',CityName) values (4,'d',CityName) values (5,'e','上海')

<div class="codetitle"><a style="CURSOR: pointer" data="45543" class="copybut" id="copybut45543" onclick="doCopy('code45543')"> 代码如下:

<div class="codebody" id="code45543">

select * from @T1

-----最优的方式

SELECT CityName,STUFF((SELECT ',' + UserName FROM @T1 subTitle WHERE CityName=A.CityName FOR XML PATH('')),1,'') AS A

FROM @T1 A

GROUP BY CityName

----第二种方式

SELECT B.CityName,LEFT(UserList,LEN(UserList)-1)

FROM (

SELECT CityName,(SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')) AS UserList

FROM @T1 A

GROUP BY CityName

) B

stuff(select ',' + fieldname from tablename for xml path('')),'')

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!