使用单选题和多选题在在线测试中找到正确回答的问题

问题描述

查看表格:http://dl.dropbox.com/u/10356431/Shared/screen.png

请帮助我构建一个 SQL，以便在特定 test_id 的在线测试中找到正确回答的问题.

Please help me to construct a SQL to find the correctly answered questions in an online test for a particular test_id.

我已经构建了一个.

SELECT COUNT(UNIQUE d.question_id) AS CORRECT
FROM test_response d,
     question_response r
WHERE d.response_id   = r.question_resp_id
AND r.correct_response_flag != 'N'
AND d.test_id = '10113'

但问题是，虽然它会准确地找到单选题，但如果它是多项选择题，则不会，因为假设 4 个回答中有 2 个是正确的，选择一个会将其视为正确回答的问题，这是不准确.

But the problem is while it will find the single choice questions accurately, it won't if its a multi-choice as suppose out of 4 responses 2 are correct, choosing one will count it as a correctly answered question which is inaccurate.

逻辑:生成问题集并显示给用户.每个测试都有自己的 ID，使用特定的问题集.用户选择的响应存储在 test_response 表中.

Logic: A question set is generated and show to the user. Each test has its own id using a particular question set. The responses chosen by the user are stored in the test_response table.

推荐答案

更新:这不适用于 OP 的表格设计，其中为 4 个答案的问题创建了 2 行

我认为您需要先检查每个问题是否所有答案都正确，然后计算没有错误答案的问题:

i think you need to first check each question if it has all answers correct and then count the questions without incorrect answers:

select
  count(*) - count(incorrect_answers_per_question) correct
from (
  select
    d.test_id,
    d.question_id,
    sum(case when r.correct_response_flag = 'N' then 1 end) incorrect_answers_per_question
  from test_response d
  join question_response r on d.response_id = r.question_resp_id
  where d.test_id = '10113'
  group by d.test_id, d.question_id
)

这篇关于使用单选题和多选题在在线测试中找到正确回答的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！