MS MySQL + 获取基于测验答案与正确答案的统计数据（以百分比表示）

Question

这个尝试有点超出我的 SQL/查询技能，所以我正在寻求关于如何完成它的建议。

概括：

有一个表格，记录用户回答一些测验问题的尝试。

• 当前问题

• 提供答案

• 正确答案

这是表格布局：

create table quiz_answers(

    id int,

    submit_date datetime,

    session_id varchar(255),

    quiz_name varchar(255),

    question_num varchar(255),

    answer varchar(255),

    correct_answer varchar(255),

    user_ip varchar(255)

);

insert into quiz_answers(id, submit_date, session_id, quiz_name, question_num, answer, correct_answer, user_ip) values

(1, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer1', 'correct_answer', 'xx.xx.xx.xxxx'),

(2, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer2', 'correct_answer', 'xx.xx.xx.xxxx'),

(3, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'correct_answer', 'correct_answer', 'xx.xx.xx.xxxx'),

(4, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer1', 'correct_answer', 'xx.xx.xx.xxxx'),

(5, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer3', 'correct_answer', 'xx.xx.xx.xxxx'),

我正在使用 PHP。我有可能的问题答案供用户选择（在数组或分隔字符串中，可能用于IN()查询部分？）

所以对于问题： q1_question0

有很多“提交”

可能的答案：answer1, answer2, answer3, answer4&correct answer

它被设置为在每次“尝试”回答问题时始终保存正确答案（认为稍后通过这种方式获取统计数据可能更容易？）。

我正在寻找一种方法来获得计数/最大值或更好的每个答案与正确答案的百分比。

即：类似于-

• answer1 - 25%

• answer2 - 50%

• answer3 - 10%

• answer4 - 5%

• correct answer - 10%

甚至：

• answer1 - 4/10

• answer2 - 2/10

• answer3 - 1/10

• answer4 - 1/10

• correct answer - 2/10

我更喜欢我认为的 %.. 所以我可以使用某种 CSS/标记来直观地创建进度条或其他东西..

此示例仅适用于 1 个问题......有“x”个可能的答案......但每个问题的可能答案数量都不同。（因此，为什么我认为在数组/分隔字符串中有可能的答案.. 用于动态查询创建）.. <- 但不要让我在这里缺乏经验以任何方式定下基调！哈哈

我不确定这是否也涉及几个子集查询？（获取每个可能答案的“统计数据”？）或者从哪里开始？

我是否尝试收集具有相同问题名称的所有条目..然后以某种方式尝试通过 IN() 子句中的特定答案将其分解并按问题名称从记录总数中获得 %？

Answer 1

因为所有的志愿者仇恨者（版主）都喜欢无缘无故地拒绝投票（除了引导他们的自负）......这是我最终使用的最终结果。然后我使用了一些 PHP 来做数学计算：$statChecker_sql = "SELECT COUNT(*) as total,(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx1')as answer1,(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx2') as answer2,(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx3') as answer3,(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx4') as answer4,(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx5') as answer5&nbsp;FROM image_quiz;";这是一个使用 PDO 的动态示例：$statChecker_sql = "SELECT COUNT(*) as TOTAL, ";for($i=0; $i<$totalPossibleAnswers; $i++){&nbsp; &nbsp; if($i != ($totalPossibleAnswers - 1)){&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; $statChecker_sql .= "(SELECT COUNT(*) FROM image_quiz WHERE answer = :answerValue$i) as answer$i, ";&nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; &nbsp; $statChecker_sql .= "(SELECT COUNT(*) FROM image_quiz WHERE answer = :answerValue$i) as answer$i "; //remove trailing comma (Im sure there is a better way)..lol!!!!!&nbsp; &nbsp; }&nbsp; &nbsp;}$statChecker_sql .= " FROM image_quiz WHERE question_num = :targetQuestion";$statChecker_stmt = $conn->prepare($statChecker_sql);//dynamically add the potential values for parameterized queryfor($i=0; $i<$totalPossibleAnswers; $i++){&nbsp; &nbsp; $statChecker_stmt->bindValue(':answerValue'.$i, $quiz['question'][0]['answer'][$i]);}$statChecker_stmt->bindValue(':targetQuestion',$targetQuestion);$statChecker_stmt->execute();$statChecker_stmt->setFetchMode(PDO::FETCH_ASSOC);$statResults = $statChecker_stmt->fetch();&nbsp;$statResultsCount = count($statResults); //current query index count// submitted answer count * 100 \ total answers count//outputecho '<br><br>Total Submitted Answers 1: ' . $statResults['TOTAL'] . '<br>';echo 'Total Submitted Answers 2: ' . $statResultsCount . '<br>';echo 'Detailed Answers breakdown: <br>';for($z=1; $z<$statResultsCount; $z++){&nbsp; &nbsp; echo $z . ' - ' . $possibleAnswerArray[$z-1] . ': ' . $statResults['answer'.($z-1)] . ' / ' . $statResults['TOTAL'] . ' (' . round(($statResults['answer'.($z-1)] * 100) / $statResults['TOTAL']) . '%)' . '<br>';}