这不一定会按照您希望的顺序分配教师，但是仍然会按照您希望的比例分配。它使用该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
602392714
清零编程群