27,579
社区成员
发帖
与我相关
我的任务
分享
--参考:
SELECT a.*,b.*
FROM
(SELECT rowid=row_number() over(PARTITION BY ClassName order BY UserID), * from 学生信息表) a --楼主还可以把这行里的order by改成order by newid(),随机性更强
LEFT JOIN
(SELECT rowid=row_number() over(PARTITION BY ClassName order BY NEWID()), cnt=COUNT(1) OVER(PARTITION BY Classname), * FROM 试卷与班级分配结果表) b
ON a.ClassName = b.ClassName
AND a.rowid % cnt + 1 = b.rowid
--假如在试卷与班级分配结果表中,每行记录代表一份试卷的话:
SELECT a.*,b.*
FROM
(SELECT rowid=row_number() over(PARTITION BY ClassName order BY UserID), * from 学生信息表) a
LEFT JOIN
(SELECT rowid=row_number() over(PARTITION BY ClassName order BY NEWID()), * FROM 试卷与班级分配结果表) b
ON a.ClassName = b.ClassName
AND a.rowid = b.rowid