110,534
社区成员
发帖
与我相关
我的任务
分享
USE [test]
go
/*
1.学生表中每名学生都对应一位指导教师
2.该学生被分配到的答辩组不能包含该学生的指导教师
*/
--待分配学生表
declare @S_List table
(
s_id varchar(10), --学生id
guide_id varchar(10) --指导老师id
)
INSERT INTO @S_List --存在5个学生进行答辩
select 's1','g1'
union
select 's2','g2'
union
select 's3','g3'
union
select 's4','g4'
union
select 's5','g5'
--待分配教师表
declare @T_List table
(
teacher_id varchar(10)
);
INSERT INTO @T_List
select 'g1'
union
select 'g2'
union
select 'g3'
union
select 'g4'
union
select 'g5'
union
select 'g6'
union
select 'g7'
union
select 'g8'
union
select 'g9'
union
select 'g10'
declare @Answer_Group table
(
group_id varchar(10),
teacher_id varchar(10)
)
DECLARE @Group_Member int = 2; --每个答辩组老师人数
DECLARE @Group_Sum int = (SELECT COUNT(teacher_id) FROM @T_List) / @Group_Member --答辩总组数
WHILE(1=1)
BEGIN
DECLARE @Max_Group_Number int = (SELECT MAX(CAST(group_id AS int)) FROM @Answer_Group)
IF (@Max_Group_Number = @Group_Sum) --组编号等于总答辩组数时退出
BREAK;
IF @Max_Group_Number IS NULL
SET @Max_Group_Number= ISNULL(@Max_Group_Number, 1) --组数为空时的处理
ELSE
SET @Max_Group_Number=@Max_Group_Number+1; --将组数自增
--随机抽选2名老师分配到答辩组中
INSERT INTO @Answer_Group
SELECT TOP(@Group_Member) @Max_Group_Number, t.teacher_id
FROM
( SELECT teacher_id FROM @T_List
EXCEPT SELECT teacher_id FROM @Answer_Group) t
ORDER BY NEWID()
END
;WITH c_Group AS
(
SELECT DISTINCT group_id,
STUFF((SELECT ';'+LTRIM(teacher_id)
FROM @Answer_Group tempC
WHERE group_id= a.group_id
FOR XML PATH('')),1,1,'') Member
FROM @Answer_Group a
)
,c2 AS
(
SELECT c.*, s.s_id --s_id列为学生编号,c.GroupID为随机组,Member列为该组分配的老师
FROM c_Group c
JOIN @S_List s ON CHARINDEX(s.guide_id, c.Member)=0
)
SELECT * FROM c2
--从c2视图中任意抽选s_id 为(s1,s2,s3,s4,s5)的5条数据即表示分组成功,试试看能否满足要求