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/10answer2 - 2/10answer3 - 1/10answer4 - 1/10correct 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

 FROM image_quiz;";

这是一个使用 PDO 的动态示例：

$statChecker_sql = "SELECT COUNT(*) as TOTAL, ";

for($i=0; $i<$totalPossibleAnswers; $i++){

    if($i != ($totalPossibleAnswers - 1)){      

        $statChecker_sql .= "(SELECT COUNT(*) FROM image_quiz WHERE answer = :answerValue$i) as answer$i, ";

    }else{

        $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!!!!!

    }   

}

$statChecker_sql .= " FROM image_quiz WHERE question_num = :targetQuestion";

$statChecker_stmt = $conn->prepare($statChecker_sql);

//dynamically add the potential values for parameterized query

for($i=0; $i<$totalPossibleAnswers; $i++){

    $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(); 

$statResultsCount = count($statResults); //current query index count

// submitted answer count * 100 \ total answers count

//output

echo '<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++){

    echo $z . ' - ' . $possibleAnswerArray[$z-1] . ': ' . $statResults['answer'.($z-1)] . ' / ' . $statResults['TOTAL'] . ' (' . round(($statResults['answer'.($z-1)] * 100) / $statResults['TOTAL']) . '%)' . '<br>';

}