MySQL中如何实现食谱与学生的唯一分配并填充至分配表？

问题原因 #

原INSERT语句的逻辑存在漏洞：执行INSERT操作时，TempAssignments表尚未插入任何数据，因此子查询中的NOT EXISTS条件始终为真，每次都会返回同一个未被分配的学生（通过LIMIT 1取第一个），最终导致所有食谱都分配给同一学生。

修正方案 #

通过为食谱表和学生表分别生成连续行号，再通过行号匹配实现一对一分配，确保每个学生最多被分配一个食谱，且所有食谱都能分配给不同学生。

修正后的完整脚本 #

/* CREATE TABLE HAVING SEVEN RECIPES */ 
CREATE TABLE TempRecipes( Recipe VARCHAR(16) );
INSERT INTO TempRecipes VALUES ('Cake'), ('Pie'), ('Cookies'), ('Ice Cream'), ('Brownies'), ('Jello'), ('Popsicles');

/* CREATE TABLE HAVING TEN STUDENTS, i.e. MORE STUDENTS THAN AVAILABLE RECIPES */ 
CREATE TABLE TempStudents( Student VARCHAR(16) );
INSERT INTO TempStudents VALUES ('Ann'), ('Bob'), ('Charlie'), ('Daphne'), ('Earl'), ('Francine'), ('George'), ('Heather'), ('Ivan'), ('Janet');

/* CREATE TABLE TO STORE THE ASSIGNMENTS */ 
CREATE TABLE TempAssignments( Recipe VARCHAR(16), Student VARCHAR(16) );

-- 修正后的INSERT语句：通过行号匹配实现唯一分配
INSERT INTO TempAssignments(Recipe, Student)
SELECT r.Recipe, s.Student
FROM (
    -- 为每个食谱生成递增行号
    SELECT Recipe, ROW_NUMBER() OVER () AS recipe_row
    FROM TempRecipes
) r
JOIN (
    -- 为每个学生生成递增行号
    SELECT Student, ROW_NUMBER() OVER () AS student_row
    FROM TempStudents
) s ON r.recipe_row = s.student_row;

逻辑说明 #

• 子查询为TempRecipes的每条食谱生成唯一递增行号（范围1-7）
• 子查询为TempStudents的每个学生生成唯一递增行号（范围1-10）
• 通过行号相等的条件关联两个表，前7名学生（行号1-7）会分别匹配到7条食谱，剩余3名学生无分配，完全符合需求。

可选扩展：随机分配 #

若需要随机分配食谱给学生（而非固定顺序），可在生成行号时加入随机排序：

INSERT INTO TempAssignments(Recipe, Student)
SELECT r.Recipe, s.Student
FROM (
    SELECT Recipe, ROW_NUMBER() OVER (ORDER BY RAND()) AS recipe_row
    FROM TempRecipes
) r
JOIN (
    SELECT Student, ROW_NUMBER() OVER (ORDER BY RAND()) AS student_row
    FROM TempStudents
) s ON r.recipe_row = s.student_row;

内容的提问来源于stack exchange，提问作者Chris