34,594
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#student') is null
drop table #student
Go
Create table #student([ID] int,[name] nvarchar(22))
Insert #student
select 1,N'张三' union all
select 2,N'李四'
GO
if not object_id(N'Tempdb..#class') is null
drop table #class
Go
Create table #class([studentID] int,[classname] nvarchar(22))
Insert #class
select 1,N'语文' union all
select 1,N'数学' union all
select 2,N'英语' union all
select 2,N'生物'
Go
--测试数据结束
SELECT ID ,
name ,
STUFF(( SELECT ',' + classname
FROM #class
WHERE studentID = #student.ID
FOR
XML PATH('')
), 1, 1, '') AS classname
FROM #student;
select
a.id
,a.name
,classname=stuff((SELECT ','+classname FROM class b WHERE a.id=b.studentid for xml path('')),1,1,'')
from student a