MySQL实现多选题需全选对才计分的测验得分查询方案

核心校验逻辑 #

要判定一道多选题用户回答正确，必须同时满足两个条件：

• 用户选中的所有选项均为正确选项（没有选到错误选项）
• 用户选中的正确选项数量等于该题设置的总正确选项数量（没有漏选正确选项）

正确查询语句 #

以下是统计user_id=1的用户在quiz_id=1的测验中答对题数的SQL：

SELECT COUNT(DISTINCT qq.id) AS correct_count
FROM quiz_question qq
WHERE qq.quiz_id = 1
-- 校验用户没有选择错误选项
AND EXISTS (
    SELECT 1
    FROM user_quiz_answers uqa
    LEFT JOIN quiz_answer qa ON uqa.answer = qa.id
    WHERE uqa.user_id = 1 AND uqa.question_id = qq.id
    HAVING SUM(IF(qa.is_correct = 0, 1, 0)) = 0
)
-- 校验用户选全了所有正确选项
AND EXISTS (
    SELECT 1
    FROM (
        SELECT COUNT(*) AS total_right FROM quiz_answer WHERE question_id = qq.id AND is_correct = 1
    ) t
    JOIN (
        SELECT COUNT(*) AS user_right 
        FROM user_quiz_answers uqa
        JOIN quiz_answer qa ON uqa.answer = qa.id
        WHERE uqa.user_id = 1 AND uqa.question_id = qq.id AND qa.is_correct = 1
    ) u ON t.total_right = u.user_right
);

结果说明 #

你提供的测试数据中，ID为1的题目总共有2个正确选项，用户仅选中1个，不满足全选要求，上述查询返回结果为0，符合预期规则。
如果需要同时查询总题数、得分率等信息，调整聚合逻辑即可实现。

内容的提问来源于stack exchange，提问作者Jersh