如何根据不同优先级的条件编写分配有限数量职位的SQL查询

Question

我创建了一个页面，在那里我可以查看学生到宿舍的申请。您可以在：http://formularz.funpic.de/dormitory.htm预览它。我正在寻找帮助编写查询来获取页面上显示的信息。房间应该首先给贫穷的学生，然后给那些住在远离宿舍的学生。我有三个宿舍："lubel"，"mleczko"，"maryks“

我当前的数据库查询如下：

SELECT * 
  FROM aplication 
  WHERE (sex='w' AND preference1='lubel') 
  GROUP BY poor DESC, distance DESC 
UNION ALL SELECT * 
  FROM aplication 
  WHERE (sex='w' AND preference2='lubel' AND poor='') 
  GROUP BY distance DESC  
UNION ALL SELECT * 
  FROM aplication 
  WHERE (sex='w' AND preference3='lubel' AND poor='') 
  GROUP BY distance DESC

我想为这个查询添加一些限制：

• 卢贝尔:12个名额
• 为mleczko: 13个名额
• 马利克:5个名额

如何编写查询才能获得这样的结果？我知道我可以使用LIMIT来限制结果的数量，但是有什么方法可以确保结果没有冗余呢？

Answer 1

通过将(preference1, preference2, preference3)分离为单独的表(我们称之为“规范化”)，您可以极大地简化这一点。

如果可能的话，您应该创建这样的表：

CREATE TABLE application (application_id ... PRIMARY KEY, sex ..., poor ...);
CREATE TABLE preference (application_id ..., dorm ..., rank ...);

现在，对于每个application，preference表中将有三行:一个带有rank = 1，一个带有rank = 2，另一个带有rank = 3。

您可能无法永久更改结构，在这种情况下，您可以使用TEMPORARY TABLE临时执行相同的操作。

CREATE TEMPORARY TABLE preference (...)
INSERT INTO preference (SELECT application_id, preference1, 1 FROM application);
INSERT INTO preference (SELECT application_id, preference2, 2 FROM application);
INSERT INTO preference (SELECT application_id, preference3, 3 FROM application);

有了这一点，查询就很简单：

SELECT application.*
FROM application
INNER JOIN preference ON (preference.application_id = application.application_id)
WHERE sex = 'w' AND dorm='lubel' AND poor=''
ORDER BY rank, poor DESC, distance DESC
LIMIT 12

但你要求的不仅仅是:实际上是一次对每个人进行排序。

为此，我建议添加一个新的assignment列(同样，这可以在必要时临时完成)，因此，通过一系列的四个查询，您可以这样做：

UPDATE application SET assignment = NULL

UPDATE application.*
INNER JOIN preference ON (preference.application_id = application.application_id)
SET assignment = 'lubel'
WHERE assignment IS NULL AND sex = 'w' AND dorm='lubel' AND poor=''
ORDER BY rank, poor DESC, distance DESC
LIMIT 12

UPDATE application.*
INNER JOIN preference ON (preference.application_id = application.application_id)
SET assignment = 'mleczko'
WHERE assignment IS NULL AND sex = 'w' AND dorm='mleczko' AND poor=''
ORDER BY rank, poor DESC, distance DESC
LIMIT 13

UPDATE application.*
INNER JOIN preference ON (preference.application_id = application.application_id)
SET assignment = 'maryks'
WHERE assignment IS NULL AND sex = 'w' AND dorm='maryks' AND poor=''
ORDER BY rank, poor DESC, distance DESC
LIMIT 5

请注意，每次您只找到那些还没有外派的人，所以一旦有人得到了他们的选择，任务列就会被填充，他们将不匹配任何未来的查询。

我假设任何人都可以得到任何宿舍(也就是说，每个人都有对所有三个宿舍的偏好；这只是一个关于这些选择的顺序的问题)。