sql查询将多个结果拼接用“，”逗号隔开

前几天碰到一个需求，把项目中，参与项目的人员都查出来，并且拼在一起用逗号隔开。然后研究了一个晚上弄出来了。我的sql水平不行，就马马虎虎的把自己的方法分享出来。举个栗子：


新建三个看着比较顺眼的表。

[SQL] 纯文本查看 复制代码

create table A_UserInfo
(
        FID int primary key identity(1,1),
        FUserName varchar(328),
        FAge int
)
insert into A_UserInfo(FUserName,FAge) values('卡卡',18)
insert into A_UserInfo(FUserName,FAge) values('叶子',18)
insert into A_UserInfo(FUserName,FAge) values('球球',18)
insert into A_UserInfo(FUserName,FAge) values('天涯',18)
create table A_ProjectCourse
(
        FID int primary key identity(1,1),
        FCourseName varchar(128),
        FType varchar(18)
)
insert into A_ProjectCourse(FCourseName,FType) values('语文','A')
insert into A_ProjectCourse(FCourseName,FType) values('数学','B')
insert into A_ProjectCourse(FCourseName,FType) values('英语','C')
create table A_UserScore
(
        FID int primary key identity(1,1),
        FUserID int,
        FCourseType varchar(18),
        FScore int        
)
insert into A_UserScore(FUserID,FCourseType,FScore) values(1,'A',15)
insert into A_UserScore(FUserID,FCourseType,FScore) values(1,'B',70)
insert into A_UserScore(FUserID,FCourseType,FScore) values(1,'C',50)
insert into A_UserScore(FUserID,FCourseType,FScore) values(2,'A',60)
insert into A_UserScore(FUserID,FCourseType,FScore) values(2,'B',40)
insert into A_UserScore(FUserID,FCourseType,FScore) values(2,'C',30)
insert into A_UserScore(FUserID,FCourseType,FScore) values(3,'A',70)
insert into A_UserScore(FUserID,FCourseType,FScore) values(3,'B',80)
insert into A_UserScore(FUserID,FCourseType,FScore) values(3,'C',90)
insert into A_UserScore(FUserID,FCourseType,FScore) values(4,'A',100)
insert into A_UserScore(FUserID,FCourseType,FScore) values(4,'B',200)

单独查询：

[SQL] 纯文本查看 复制代码

select * from A_UserInfo
select * from A_ProjectCourse
select * from A_UserScore

连接查询：

[SQL] 纯文本查看 复制代码

select T1.FID,T1.FUserName,T3.FCourseName,T2.FScore
from A_UserInfo T1 
left join A_UserScore T2 on T1.FID=T2.FUserID
left join A_ProjectCourse T3 on T2.FCourseType=T3.FType
order by T1.FID asc

一：简单的拼接所有人

[AppleScript] 纯文本查看 复制代码

  SELECT distinct stuff((SELECT ',' + t1.FUserName
                                 FROM (select FID,FUserName from A_UserInfo ) AS t1
                 WHERE t1.FID = T1.FID FOR xml path('')), 1, 1, '')
  FROM  (select FID,FUserName from A_UserInfo) as T1
  GROUP BY T1.FID

二：我做需求的时候用到了函数。
整体上就是，传入用户ID进来，然后去查询要拼接的字符串

[SQL] 纯文本查看 复制代码

 create function [dbo].[JointCharacter](@userID int)
  returns varchar(2048)
  as
  begin        
        declare @Names varchar(2048)        
        SELECT @Names = stuff((SELECT     ',' + t.FCourseName  FROM
                              (select T1.FID,T1.FUserName,T3.FCourseName,T2.FScore
                              from A_UserInfo T1 
                              left join A_UserScore T2 on T1.FID=T2.FUserID
                              left join A_ProjectCourse T3 on T2.FCourseType=T3.FType
                              where T1.FID=@userID) AS t
         WHERE    t.FID = T.FID FOR xml path('')), 1, 1, '')
FROM  (select T1.FID,T1.FUserName,T3.FCourseName,T2.FScore
           from A_UserInfo T1 
           left join A_UserScore T2 on T1.FID=T2.FUserID
           left join A_ProjectCourse T3 on T2.FCourseType=T3.FType
           where T1.FID=@userID) as T
                GROUP BY T.FID
        return @Names
  end

二.1：查询FID为1 的人考了哪几门

[SQL] 纯文本查看 复制代码

select dbo.JointCharacter(1)

二.2：查询所有人考了哪几门

[SQL] 纯文本查看 复制代码

  select FID,FUserName,dbo.JointCharacter(FID) FExamName from A_UserInfo

可以可以 这篇教程用心了

加精并奖励5元人民币

SELECT (SELECT CAST(FUserName AS VARCHAR)+','
FROM A_UserInfo FOR XML PATH('')) AS '一群小鬼'

向你学习