这不一定会按照您希望的顺序分配教师，但是仍然会按照您希望的比例分配。它使用该
NTILE函数将名称放入存储桶中，并为每行分配适当的存储桶编号。
如果您想下订单，则必须为教师和学生提供一个唯一的ID列。
with tch as( select t.*, row_number() OVER ( ORDER BY name ) as n from teachers t ),ct AS ( select count(*) as cnt from Teachers ) select s.name as student,tch.name as teacher from ( SELECt name, NTILE(cnt) OVER (partition by cnt ORDER BY name) AS n FROM Students cross join ct) s join tch on tch.n = s.n;
演示版
Student| Teacher ----- | ---- Adam | Ethan Bennet | Ethan Elmer | Ethan Jack | Kyle Justin | Kyle Louis | Leo Noah | Leo
