展开全部
也不说你是用的什么数据库,sqlserver为例
建表create table class
(classid int,
classname varchar(10),
teacherid int)
insert into class values (1,'一班62616964757a686964616fe58685e5aeb931333335326234',1)
insert into class values (1,'一班',2)
insert into class values (2,'三班',1)
create table teacher
(teacherid int,
teachername varchar(10))
insert into teacher values (1,'zhangsan')
insert into teacher values (2,'lisi')
执行with tb as
(select a.classid,a.classname,b.teachername
from class a left join teacher b
on a.teacherid=b.teacherid)
select classid,classname,teachername=stuff((select ','+teachername from tb t where classid=tb.classid and classname=tb.classname for xml path('')), 1, 1, '')
from tb
group by classid,classname
结果截图