我有两个表
表1-mdl_question_attempts
id questionid rightanswer responsesummary
1 1 A A
2 1 A B
3 1 A A
4 1 A B
5 2 A A
6 1 A A
7 2 D E
8 2 D D
9 2 D E
10 3 F F
11 3 F G
表2-mdl_question_attempt_steps
id questionattemptid userid
5 1 1
6 2 1
7 3 2
8 4 1
9 5 2
10 6 1
11 7 1
12 8 1
13 9 1
14 10 1
15 11 1
表1——mdl_question_attempts，主键——id字段与相关
表2——mdl_question_attempt_steps，外键——questionattemptid
表1是关于用户对某些问题的回答。
rightanswer-是特定问题的正确答案，responsesummary是用户对该问题给出的答案。"questionid"表示问题编号。有时同一用户多次尝试一个问题，每次尝试的答案如表1所示。
对于每个问题，可以从表2 中找到"userid"或用户
Eg: 1st row in table1 done by userid =1
所以我的问题是，我想根据一个学习者回答一个问题两次的次数，找出一个学习器（一个用户，例如：userid=1）回答同一个问题错误两次的百分比或比率？
表1中突出显示的部分显示了userid=1相关数据
用户1回答问题1-4次，错误2次
用户1回答问题2-3次，错误2次
问题3回答了2次，只答错了1次。所以我想同一个问题错两次。因此
问题3不被视为
questionid wrong count
1 2/4
2 2/3
所以我对userid=1的最终输出是
=((2/4)+(2/3))/2
=0.583
=错误计数的总和除以平均值或2倍（只有2个问题回答）如果3个问题回答的总和应该除以3。
我写了以下三个代码，可以分别获得输出。但我想在一个查询中得到这个
function quiztwicewrong()
{
$con=mysqli_connect("localhost:3306","root","", "moodle");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
//quiz twice wrong
查询1
$resultq = mysqli_query ($con,"SELECT mdl_question_attempts.rightanswer as rightanswer,count(mdl_question_attempts.questionid) as questionid1 FROM mdl_question_attempts,mdl_question_attempt_steps WHERE mdl_question_attempt_steps.questionattemptid=mdl_question_attempts.id and mdl_question_attempt_steps.userid='1' and mdl_question_attempts.rightanswer<>mdl_question_attempts.responsesummary GROUP BY mdl_question_attempts.questionid HAVING questionid1>1 ") or die("Error: ". mysqli_error($con));
while($rowq= mysqli_fetch_array( $resultq))
{
echo $rowq['questionid1']."-".$rowq['rightanswer']."<br>"."<br>"."<br>";
}
查询2
$resultqall = mysqli_query ($con,"SELECT mdl_question_attempts.rightanswer as rightanswer,count(mdl_question_attempts.questionid) as questionid1 FROM mdl_question_attempts,mdl_question_attempt_steps WHERE mdl_question_attempt_steps.questionattemptid=mdl_question_attempts.id and mdl_question_attempt_steps.userid='1' GROUP BY mdl_question_attempts.questionid HAVING questionid1>1") or die("Error: ". mysqli_error($con));
while($rowqall= mysqli_fetch_array( $resultqall))
{
echo $rowqall['questionid1']."-".$rowqall['rightanswer']."<br>"."<br>"."<br>";
}
//query 3
$resultqdup = mysqli_query ($con,"SELECT count(*) as duplicate FROM
(select mdl_question_attempts.rightanswer as ightanswer from mdl_question_attempts,mdl_question_attempt_steps WHERE mdl_question_attempt_steps.questionattemptid=mdl_question_attempts.id and mdl_question_attempt_steps.userid='1' and mdl_question_attempts.rightanswer<>mdl_question_attempts.responsesummary GROUP BY mdl_question_attempts.questionid HAVING COUNT(mdl_question_attempts.questionid)>1) as questionid1 ") or die("Error: ". mysqli_error($con));
while($rowqdup= mysqli_fetch_array( $resultqdup))
{
echo $rowqdup['duplicate'];
}
mysqli_close($con);
}
return quiztwicewrong();
3个查询的输出为
查询1-输出
2-A
2-D
查询2-输出
4-A
3-D
2-F (I don’t want this part-this comes for the 3rd question, but I want only the output related to query 1- ouput,only answer more than 1 time wromg)
查询3输出
2
所以我想组合3个输出，需要计算并获得值
=((2/4)+(2/3))/2
=0.583
请通过编辑我的代码来帮助我做到这一点，或者有什么建议吗？
非常感谢。在另一个论坛的朋友的帮助下，我解决了这个问题。如果你认为这个解决方案对任何人来说都是必要的，我发布了这个解决方案
SELECT ROUND(SUM(incorrect/answered)/COUNT(*), 3) as result
FROM
(
SELECT qa.questionid,
SUM(IF(qa.rightanswer <> qa.responsesummary, 1, 0)) as incorrect ,
COUNT(*) as answered
FROM mdl_question_attempts qa
INNER JOIN mdl_question_attempt_steps qas
ON qa.id = qas.questionattemptid
WHERE qas.userid = $user
GROUP BY qa.questionid
HAVING incorrect > 1
) as totals