mysql - 选择与正确答案相匹配的答案

Question

我正在开发在线考试网站，但在编写查询来检查 MCQ 问题中提供的答案是否正确时遇到问题。该问题可能包含多于 1 个正确答案，如果只有一个正确答案是错误的，则问题答案是错误的

CREATE TABLE `question` (
  `id` int(11) NOT NULL,
  `question` varchar(255) DEFAULT NULL,
  `image` longblob,
  `type` int(1) DEFAULT NULL COMMENT '0 - MCQ / 1 - T/F /2- COMPLETE/',
  `instructorID` int(11) DEFAULT NULL,
  `courseID` int(11) DEFAULT NULL,
  `TF` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `answers` (
  `id` int(11) NOT NULL,
  `questionID` int(11) DEFAULT NULL,
  `answer` varchar(255) DEFAULT NULL,
  `image` longblob,
  `isRight` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `result` (
  `id` int(11) NOT NULL,
  `testID` int(11) DEFAULT NULL,
  `studentID` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `settingID` int(11) DEFAULT NULL,
  `period` int(11) DEFAULT NULL,
  `instructorID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `result_answers` (
  `id` int(11) NOT NULL,
  `resultID` int(11) DEFAULT NULL,
  `questionID` int(11) DEFAULT NULL,
  `answerID` int(11) DEFAULT NULL,
  `TF` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `answers_ibfk_1` (`questionID`);

ALTER TABLE `question`
  ADD PRIMARY KEY (`id`),
  ADD KEY `question_ibfk_1` (`instructorID`),
  ADD KEY `question_ibfk_2` (`courseID`);

ALTER TABLE `result`
  ADD PRIMARY KEY (`id`),
  ADD KEY `testID` (`testID`),
  ADD KEY `studentID` (`studentID`),
  ADD KEY `instructorID` (`instructorID`),
  ADD KEY `settingID` (`settingID`);

ALTER TABLE `result_answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `questionID` (`questionID`),
  ADD KEY `answerID` (`answerID`),
  ADD KEY `result_answers_ibfk_1` (`resultID`);

ALTER TABLE `answers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=516;

ALTER TABLE `question`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

ALTER TABLE `result`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `result_answers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

ALTER TABLE `answers`
  ADD CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`questionID`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE `question`
  ADD CONSTRAINT `question_ibfk_1` FOREIGN KEY (`instructorID`) REFERENCES `instructor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `question_ibfk_2` FOREIGN KEY (`courseID`) REFERENCES `course` (`id`);


ALTER TABLE `result`
  ADD CONSTRAINT `result_ibfk_1` FOREIGN KEY (`testID`) REFERENCES `test` (`id`),
  ADD CONSTRAINT `result_ibfk_2` FOREIGN KEY (`studentID`) REFERENCES `student` (`id`),
  ADD CONSTRAINT `result_ibfk_3` FOREIGN KEY (`instructorID`) REFERENCES `instructor` (`id`),
  ADD CONSTRAINT `settings` FOREIGN KEY (`settingID`) REFERENCES `settings_profiles` (`id`);


ALTER TABLE `result_answers`
  ADD CONSTRAINT `result_answers_ibfk_1` FOREIGN KEY (`resultID`) REFERENCES `result` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `result_answers_ibfk_2` FOREIGN KEY (`questionID`) REFERENCES `question` (`id`),
  ADD CONSTRAINT `result_answers_ibfk_3` FOREIGN KEY (`answerID`) REFERENCES `answers` (`id`);
COMMIT;

complete database sql
我做了一些搜索，发现这是一个称为关系除法的问题，所以我尝试创建一个函数，如果结果中的问题得到正确解决，则返回 true

BEGIN

   DECLARE RES Boolean;

   IF (SELECT resultID
            From result_answers
            WHERE answerID IN (SELECT id FROM answers where isRight = 1 and questionID = qID) AND resultID = resID
            GROUP BY resultID
            HAVING COUNT(*) = (SELECT COUNT(*) FROM answers where isRight = 1 and questionID = qID)) THEN
      SET RES = True;
   ELSE
      SET RES = False;

   END IF;

   RETURN RES;

END

如果提供了一个正确的答案，即使另一个答案是错误的，它也会返回 true

当且仅当学生的答案与特定结果中特定问题的所有正确答案匹配时，我需要此查询返回 true

Answer 1

据我所知，您有三种情况:

1. 选择了正确答案
2. 选择了错误的答案
3. 未选择正确答案(这意味着需要外部联接)。

下面的查询收集此信息:

SELECT 
   SUM(case when isRight = 1 and resultID is not null then 1 else 0 end) as rightAnswers, 
   SUM(case when isRight = 0 and resultID is not null then 1 else 0 end) as wrongAnswers,
   SUM(case when isRight = 1 and resultID is null then 1 else 0 end) as missedAnswers
FROM
   answers a left join 
   (select * from result_answers where resultID = 3) ra on a.questionID = ra.questionID AND a.ID = ra.answerID
WHERE a.questionID = 2

如果 rightAnswers > 0 且 badAnswer = 0 且 misssedAnswers= 0，则问题已得到正确回答。如果您愿意，可以将这些情况组合成一个变量。 HTH。