SQL合并查询数据，以逗号分隔

功能描述：

将查询到的数据合并为一条数据，以逗号分隔

案例：

功能描述：

现有三张表：

Student（学生表）

Course（课程表）

Student-Course（学生课程关联表）

要求查询学生详细信息，课程名称以逗号分隔，查询到结果如下图所示：

 

解一：采用标量函数

需求完成步骤：

1. 创建标量函数

Create function GetCourseStr
(
 @studentID nvarchar(50)
)
returns varchar(8000)
as
begin
 declare @r varchar(8000)
 set @r='';
       select @r=@r+','+Course.CourseName
        from Course inner join [Student-Course] on [Student-Course].CID=Course.CourseID
        where [Student-Course].SID=@studentID
    return stuff(@r, 1, 1, '')
end

2. 书写查询语句

select dbo.Get_DataStr(STORES.PK_STORE,1) from STORES

查询到的效果图如下：

 

解二：使用for xml

       将查询消息放在临时表

select   Student.StudentID, Student.StudentName, Student.StudentAge, Student.StudentSex, Course.CourseName

into #temp_Student from Student

inner join [Student-Course] on [Student-Course].SID=Student.StudentID

inner join Course on Course.CourseID=[Student-Course].CID

     

        通过临时表信息进行查询

select StudentID,StudentName,StudentAge,StudentSex,left(CourseList,len(CourseList)-1)as CourseName From (

  select StudentID,StudentName,StudentAge,StudentSex   

       ,(select CourseName+',' from #temp_Student where StudentID=A.StudentID  for xml path('') ) as CourseList  

  from #temp_Student A

 group by StudentID,StudentName,StudentAge,StudentSex

) B

drop table #temp_Student

 

for xml path('') 返回类型为nvarchar(max)

 

拓展一：

sql Stuff()函数：删除指定长度的字符，并在指定的起点处插入另一组字符

 

语法：

 

STUFF ( character_expression , start , length ,character_expression )

 

eg: SELECT STUFF('abcdef', 2, 3, 'ijklmn') -----------> aijklmnef

 

拓展二：

使用 FOR XML PATH 实现字符串合并

eg：

SELECT 
    l.*,
    STUFF(
        (
            SELECT ',' + st.ShipToName
            FROM ShipTo st
            WHERE st.LocationId = l.LocationId
            AND st.IsEnable = 1
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS CombinedShipToNames
FROM 
    Locations l
WHERE 
    l.IsEnable = 1;

语句分解解释

1. 主查询部分：

   SELECT l.*, ... FROM Locations l WHERE l.IsEnable = 1;

   • 从 Locations 表（别名 l）中选择所有列

   • 只筛选 IsEnable = 1 的记录

2. 子查询部分：

   (SELECT ',' + st.ShipToName FROM ShipTo st 
    WHERE st.LocationId = l.LocationId AND st.IsEnable = 1
    FOR XML PATH(''), TYPE)

   • 对于每个 Location，查找关联的 ShipTo 记录

   • 在每条 ShipToName 前添加逗号

   • FOR XML PATH('') 将结果转换为 XML 格式

   • TYPE 指示返回 XML 类型而非字符串

3. XML 值提取：

   .value('.', 'NVARCHAR(MAX)')

   • 从 XML 结果中提取文本值

   • 转换为 NVARCHAR(MAX) 类型

4. STUFF 函数：

   STUFF(..., 1, 1, '')

   • 移除结果字符串开头的第一个逗号

   • 参数解释：从位置1开始，删除1个字符，替换为空字符串