mysql - 如何编写基于不同优先级标准分配有限数量位置的 SQL 查询

Question

我创建了一个页面，可以在其中查看学生的宿舍申请。您可以在以下位置预览:http://formularz.funpic.de/dormitory.htm 。我正在寻求帮助编写查询以获取页面上显示的信息。房间应该首先给贫困学生，然后给那些住得离宿舍远的学生。我有 3 个宿舍:“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个地方
• 对于 maryks: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，一行的 等级 = 2，以及 等级 = 3。

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

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

但您要求的不止于此:真正立即对每个人进行排序。

为此，我建议添加一个新的分配列(同样，如果需要的话可以临时完成)，因此您可以执行一系列四个查询:

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

请注意，每次您只找到尚未分配任务的人员，因此一旦有人做出选择，分配列就会被填充，并且他们将不会匹配任何 future 的查询。

我假设任何人都可以得到任何宿舍(即每个人对所有三个宿舍都有偏好；这只是这些偏好的顺序问题)。