mysql - 检查一个表中的单个值，表中的多个值以逗号分隔

Question

我有两个表格，其中一个包含问题的正确答案，另一个包含用户提交的答案。

我想用正确答案的表格计算用户输入的正确答案，并创建一个新列，status ，其状态为“正确”或“不正确”。

问题是正确答案表中的值由逗号分隔，例如 a,b,c如果用户输入值，则将其作为单个条目发送。例如 a然后是一个新条目 b .我想比较值 a和 b出现在用户表提交的用户答案中，条目为 a,b,c 的正确答案表如果用户输入选项 d而不是 b和 d不在 a,b,c 中即使用户输入 a在a,b,c那么答案也应该被标记为不正确。所以即使用户选择的一个选项是错误的，那么正确选项表中的完全正确的选项那么状态应该是不正确的。

我做了一个SQL fiddle of the tables我针对它尝试了这个查询

SELECT  qa.question_id,qa.type,qa.answers
FROM    questions_answer qa, user_test_answers uta 

            where FIND_IN_SET(uta.answers, qa.answers) > 0 and qa.question_id='4' and uta.user_id='2' and qa.question_id=uta.question_id

这返回了空结果。

我期望的结果是

question_id  test_id  user_answer     type             correct_answer     status   
  13            4        a         multiple_choice          c            incorrect 
  13            4        c         multiple_choice          c             correct   
  14            4        a         multiple_choice          a,c           correct  
  15            4        b          true_false              a             incorrect

Answer 1

您可以尝试在 MySQL 中使用 GROUP_CONCAT:

SELECT uta.question_id, uta.test_id, GROUP_CONCAT(uta.answers ORDER BY uta.answers) AS user_answer, qa.type, qa.answers correct_answer,
CASE WHEN GROUP_CONCAT(uta.answers) = qa.answers THEN 'correct' ELSE 'incorrect' END
AS status
FROM user_test_answers uta
LEFT JOIN questions_answer qa
ON qa.question_id = uta.question_id
GROUP BY uta.user_id, uta.question_id

检查我在 GROUP_CONCAT 中使用了 ORDER BY，这意味着在 questions_answer 表中，答案必须按字母顺序排列。

此外，user_test_answers 中似乎有一个无效条目，question_id 为 20。

HTH

编辑:

SELECT uta.question_id, uta.test_id, uta.user_answer, qa.type,
qa.answers correct_answer,
CASE WHEN uta.user_answer = qa.answers THEN 'correct' ELSE 'incorrect' END
AS status
FROM questions_answer qa
LEFT JOIN 
(
  SELECT user_id, type, test_id, question_id,
  GROUP_CONCAT(answers ORDER BY answers) AS user_answer,
  timestamp from user_test_answers
  GROUP BY user_id, question_id
) uta
ON qa.question_id = uta.question_id

编辑 2:

SELECT uta.question_id, uta.test_id, uta.user_answer, qa.type,
qa.answers correct_answer,
CASE WHEN uta.user_answer = qa.answers THEN 'correct' ELSE 'incorrect' END
AS status
FROM questions_answer qa
LEFT JOIN 
(
  SELECT user_id, type, test_id, question_id,
  GROUP_CONCAT(answers ORDER BY answers) AS user_answer,
  timestamp from user_test_answers
  WHERE user_id = 2 AND test_id = 4 -- for user 2 and test 4
  GROUP BY user_id, question_id
) uta
ON qa.question_id = uta.question_id
WHERE test_id = 4 -- try omitting this one in case you get incorrect results

恕我直言，您需要查看并改进您的数据库设计和架构。