--新建ClassInfo表
create table ClassInfo
(
CID int identity(1,1), --本表ID
CNumber int, --班级人数
CName varchar(10) --班级名称
);
--给表ClassInfo新增测试数据
insert ClassInfo
select 20,'一年级班'
union select 20,'一年级班'
union select 20,'一年级班'
union select 20,'一年级班';
--新增表Students(CID 班级表外键,SID学生信息表ID学生的学号就是,SName)
create table Students
(
[SID] int identity(1,1), --学号
CID int, --班级ID
SName varchar(10) --姓名
);
--给Students新增测试数据
insert into Students
select 1,'张'
union select 1,'张'
union select 1,'张'
union select 1,'张'
union select 2,'张'
union select 2,'张';
按班级显示人员信息
SELECT a.CID,
STUFF((SELECT ','+SName
FROM Students AS b
WHERE b.CID = a.CID
FOR XML PATH('')),1,1,'') AS SName
FROM Students AS a
GROUP BY a.CID