22,209
社区成员
发帖
与我相关
我的任务
分享
select id,name,
学生=stuff((select distinct ','+name from student where t.id=classroomid for xml path('')),1,1,'')
from classroom t
--试试?
DECLARE @STR VARCHAR(MAX)
SELECT A.*,@STR=ISNULL(@STR+',','')+B.NAME
FROM classroom A LEFT JOIN student B
ON A.ID=B.classroomid
--
PRINT @STR
select *,
学生=stuff((select ','+name from student where a.id=classroomid for xml path('')),1,1,'')
from classroom a
select 教室ID,教室名, 学生=stuff((select ','+学生 from tb t where 教室ID=tb.教室ID for xml path('')), 1, 1, '')
from
(select c.id 教室ID,c.name 教室名,s.name 学生 from classroom c left join student s
on c.id = s.classroomid) tb
group by 教室ID,教室名