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条数据即表示分组成功，试试看能否满足要求

------解决方案--------------------
画个er图