34,588
社区成员
发帖
与我相关
我的任务
分享
insert exam_room(exam_id,room_id,stu_id)
select room_id,exam_id,stu_id
from (select room_id,exam_id,bid=isnull((select sum(room_sum) from room where room_id < a.room_id),0)+1,room_sum from room ) a,
stu b
where a.stu_id between a.bid and a.bid+a.room_sum
order by newid()
--对学生表随机排序,并加自增字段
select fid=identity(int,1,1),* into #t from 学生表 where stu_id<=55 order by newid()
--再按fid填入考场表
select a.*,b.stu_id from 考场表 a inner join #t b on a.exam_room_id=b.fid