时间戳

首页 > 解决方案 > 对于媒人中的每个选项，确保每个选项至少匹配一个（但不超过一个）

mysql - 对于媒人中的每个选项，确保每个选项至少匹配一个（但不超过一个）

问题描述

这需要一点解释（此外，因为我不能在问题的标题中使用“问题”这个词）：

我有一个包含以下表格的媒人测验（简化）：

CREATE TABLE `Quiz` (
  `quiz_id` int(10) unsigned NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`quiz_id`),
  UNIQUE KEY `Quiz_1` (`code`)
);

CREATE TABLE `Quiz_Question` (
  `quiz_id` int(10) unsigned NOT NULL,
  `question_id` int(10) unsigned NOT NULL,
  `question` varchar(250) DEFAULT NULL,
  `type` int(10) unsigned NOT NULL, -- Lookup table of type of question: booean, radio, select, multiselect
  PRIMARY KEY (`question_id`)
);

CREATE TABLE `Quiz_Answer` (
  `question_id` int(10) unsigned NOT NULL,
  `answer_id` int(10) unsigned NOT NULL,
  `answer` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`answer_id`)
);

CREATE TABLE `Quiz_Response` (
  `user_id` int(10) unsigned NOT NULL,
  `quiz_id` int(10) unsigned NOT NULL,
  `question_id` int(10) unsigned NOT NULL,
  `answer_id` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `Response_1` (`user_id`,`question_id`,`answer_id`),
  KEY `Response_2` (`question_id`,`answer_id`)
);

到目前为止，一切都非常简单。

以前，查询是这样的（简化）：

SELECT u.login, COUNT( u.user_id ) AS matches, ...
FROM User u
INNER JOIN Quiz_Response rep ON u.user_id = rep.user_id
WHERE u.active = 1
AND ( 
    (rep.question_id = 3 AND rep.answer_id IN (20, 24)) OR 
    (rep.question_id = 10 AND rep.answer_id IN (83,84,85))
)
GROUP BY u.user_id
HAVING matches >= 2
ORDER BY u.login

注意： 我已经从 CREATE TABLE 和查询中删除了某些东西是否处于活动状态、显示顺序、被阻止的用户、日期范围等内容，以专注于核心问题。

因此，如果用户以 20 或 24 回答问题 3，他们会在结果中出现一次，如果他们以 83、84 或 85 回答问题 10，他们会再次出现。然后查询计算任何给定用户出现的次数，如果它等于或大于尝试匹配的问题数，则认为匹配（在这种情况下，匹配器检查了两个可能的问题，因此它们应该至少2 个条目（匹配项）。

我的问题是我要引入多项选择匹配。这具有单个问题的最终结果，可以有多个匹配项，这会导致计数失败。

因此，如果搜索者说他们正在寻找用 A、B 或 C 回答问题 5 的人，并且用户说他们喜欢 A、B 和 C，那么这将变成三个匹配项，基本上抵消了其他两个问题（搜索三件事，并从同一个问题中得到三场比赛）。

所以我要问的问题是我如何检查每个给定的问题，它只得分 1 匹配，即使单个问题的多个答案匹配多次。

希望一切都说得通。

标签： mysqlselect

解决方案

与其指望u.user_id，不如指望distinct rep.question_id：

SELECT u.login, u.user_id, COUNT(distinct rep.question_id) AS matches
FROM User u
INNER JOIN Quiz_Response rep ON u.user_id = rep.user_id
WHERE u.active = 1
AND ( 
    (rep.question_id = 3 AND rep.answer_id IN (20, 24)) OR 
    (rep.question_id = 10 AND rep.answer_id IN (83,84,85))
)
GROUP BY u.user_id
HAVING matches >= 2
ORDER BY u.login;

因此，如果我的Quiz_Response表如下所示：

+-------------+---------+-------------+-----------+---------+
| response_id | quiz_id | question_id | answer_id | user_id |
+-------------+---------+-------------+-----------+---------+
|           1 |       1 |           1 |         4 |       3 |
|           2 |       2 |           3 |        20 |       2 |
|           3 |       2 |           3 |        24 |       2 |
|           4 |       4 |          10 |        83 |       1 |
|           5 |       4 |          10 |        84 |       1 |
|           6 |       4 |          10 |        85 |       1 |
|           7 |       2 |           3 |        20 |       4 |
|           8 |       1 |           1 |         1 |       4 |
|           9 |       2 |           3 |        24 |       4 |
|          10 |       4 |          10 |        83 |       4 |
+-------------+---------+-------------+-----------+---------+

上述查询的输出将是：

+---------------------+---------+---------+
| login               | user_id | matches |
+---------------------+---------+---------+
| 2018-01-01 00:00:00 |       4 |       2 |
+---------------------+---------+---------+

推荐阅读