我如何比较MySQL中两列的两个查询?

What's the best way to compare two queries by two columns? these are my tables:

比较两个列的两个查询的最佳方法是什么?这些是我的表:

This table shows exam questions

这张表显示了考试题目

idEvaluation | Question | AllowMChoice | CorrectAnswer|
1                1            0             3 
1                2            1             4
1                2            1             5
1                3            0             9

This table shows a completed exam

这个表格显示了一个完整的考试。

  idExam| idEvaluation | Question | ChosenAnswer|
    25        1              1            2
    25        1              2            4
    25        1              2            5
    25        1              3            8      

I have to calculate the percentage of correct Answers, considering to certain questions may allow multiple selection.

我必须计算正确答案的百分比，考虑到某些问题可能允许多重选择。

Correct Answers / Total Answers * 100

正确答案/总答案* 100

thanks for your tips!

谢谢你的提示!

1 个解决方案

#1

This code will show you a listing of Questions and whether or not they were answered correctly.

这段代码将向您展示问题的列表，以及它们是否正确的回答。

select
    A.Question,
    min(1) as QuestionsCount,
    -- if this evaluates to null, they got A) the answer wrong or B) this portion of the answer wrong
    -- we use MIN() here because we want to mark multi-answer questions as wrong if any part of the answer is wrong. 
    min(case when Q.idEvaluation IS NULL then 0 else 1 end) as QuestionsCorrect
from
    ExamAnswers as A
    left join ExamQuestions as Q on Q.Question = A.Question and Q.CorrectAnswer = A.ChosenAnswer
group by
    A.Question -- We group by question to merge multi-answer-questions into 1

Output Confirmed:

输出确认:

Note, the columns are intentionally named this way, as they are to be included as a subquery in part-2 below.

注意，这些列是故意这样命名的，因为它们将作为part-2中的子查询被包含进来。

---

This code will give you the test score.

这段代码将给出测试分数。

select
    sum(I.QuestionsCorrect) as AnswersCorrect,
    sum(I.QuestionsCount) as QuestionTotal,
    convert(float,sum(I.QuestionsCorrect)) / sum(I.QuestionsCount) as PercentCorrect -- Note, not sure of the cast-to-float syntax for MySQL
from
    (select
        A.Eval,
        A.Question,
        min(1) as QuestionsCount,
        min(case when Q.idEvaluation IS NULL then 0 else 1 end) as QuestionsCorrect
    from
        ExamAnswers as A
        left join ExamQuestions as Q on Q.Question = A.Question and Q.CorrectAnswer = A.ChosenAnswer
    where 
        A.Eval = 25
    group by
        A.Question, A.Eval) as I
group by        
    I.Eval

Output Confirmed:

输出确认:

This will communicate the general concept. Your column names idEvaluation and Eval are difficult for me to understand, but I'm sure you can adjust the code above to suit your purposes.

这将传达总体概念。您的列名idEvaluation和Eval对我来说很难理解，但我相信您可以根据您的目的调整上面的代码。

Note, I did this in sql server, but I used fairly basic SQL functionality, so it should translate to MySQL well.

注意，这是我在sql server中做的，但是我使用了相当基本的sql功能，所以它应该可以很好地转换为MySQL。