php - 如何使用 MySQL 将学生分配给最喜欢的室友

Question

可能是一个很难解决的问题...而且我意识到这里可能有不止一个正确的答案，但最主要的是如何自动化这个过程。

我在 InnoDb 表中有很多学生。在另一张表中，我列出了他们最喜欢的室友选择。

• 大多数人选择了两三个最喜欢的室友。
• 一些人没有选择。
• 有些是由最多四个其他人选择的。
• 一些无人选择。
• 大多数学生是由一两个其他学生选择的。

在三分之二的 table 上，我有要被填满的房间。房间有两到三张床。没有任何一张床或四张床的房间。

就约束而言，一切正常。 问题是，我如何使用 MySQL(或其他编程，最好是 PHP)自动执行此过程，以便让每个人都满意？

我假设第一个选择的室友是学生最想要的，因此我根据提及的顺序将这些选择从 1 到 4 打分。当然，有时这次撞车和两个学生在第一名上有相同的选择。然后是未被任何人选择的问题，但您可以假设他们会对此成熟。

Answer 1

首先，我会保存数据库的备份，以防万一您的程序出错:

mysql -h host -u username -p password database_name > database_name_backup.sql

如果您还没有房间和学生的连接表，您还需要一张连接表。所以像这样:

CREATE TABLE room_pupil (
  room_id int NOT NULL,
  pupil_id int NOT NULL
);

请注意，虽然您在问题中很好地描述了表，但为表定义(复制表和关系的语句)提供准确的 DDL 有助于人们回答这些类型的问题。仅供将来引用，这里没有任何伤害:)

从那里，您可以运行以下类型的脚本来创建一个存储过程，然后您可以调用它来获得一个好的解决方案

(注意:这实际上对你不起作用!你需要修复它以使匹配更好，并根据你的特定数据类型对其进行调整!甚至可能是这里的一两个语法错误，我写的有点瞎了。不过，学会钓鱼不如得到一条鱼^^)

DELIMITER $$ 
DROP PROCEDURE IF EXISTS RoommateMatching$$ 
CREATE PROCEDURE RoommateMatching() 
BEGIN 
  DECLARE no_more_pupils int DEFAULT 0;
  DECLARE current_pupil CURSOR FOR 
SELECT pupil_id FROM pupils; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET no_more_pupils = 1; 

/* create a temp table to hold the proposals */ 
CREATE TABLE temp_matches ( 
  proposing_pupil_id int NOT NULL,
  proposed_pupil_id int NOT NULL 
); 
OPEN current_pupil; 

FETCH current_pupil INTO this_pupil; 

REPEAT 

/* Coalesce -1 in so its easy to tell whether they had choices picked or not */
SELECT COALESCE(pupil_choice_id, -1) INTO next_favorite_roommate_id 
  FROM roommate_preference /* TODO Your second table, don't know its name */ 
  WHERE pupil_id = this_pupil; /* TODO since there may be more than one match here or none, might need another iterator */

SELECT COALESCE(proposed_pupil_id, -1) INTO existing_proposal 
  FROM temp_matches
  WHERE proposed_pupil_id = next_favorite_roommate_id;

IF existing_proposal < 0 THEN 
  INSERT INTO temp_matches(proposing_pupil_id, proposed_pupil_id) 
  VALUES (this_pupil, next_favorite_roommate_id); 
END IF; 
IF existing_proposal > 0 THEN
  /* TODO see if there's a better match for existing_proposal here, you write it! */
END IF;
FETCH current_pupil INTO this_pupil; 
UNTIL no_more_pupils = 1 

END REPEAT; 

CLOSE current_pupil; 
/* start an iterator on the matches and insert records into your room_pupil table! */
/* TODO you write that code! */
DROP TABLE temp_matches; 
END$$ 
DELIMITER; 

至少应该让您走上正确的轨道!让我知道是否有任何需要更多说明的地方，但就像我说的那样，我不会为您编写完整的算法，请使用维基百科和 mysql 手册页来学习进入存储过程明星之路! ;)

存储过程框架的来源:http://forums.mysql.com/read.php?98,358569,358569