结合mysql中3个查询的3个输出并进行一些计算

Question

我有两张桌子

表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是用户对该问题的回答。“发问”代表问题否。有时，同一个用户尝试了几次一个问题，他们在每次尝试中的回答都如表1所示。

对于每个问题，“userid”或user可以从表2中找到

Eg: 1st row in table1 done by userid =1

因此，我的问题是，我想要找出学习者(一个用户-例如:userid =1)回答同一问题的百分比或比率，根据学习者回答问题两次的次数，回答相同的问题两次错误？

表1中突出显示的数据显示了与userid=1相关的数据

User1回答了问题1-4次，回答错了2次。

User1回答了问题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- ouput

2

所以我想把三个输出组合起来，需要计算和得到这个值。

=((2/4)+(2/3))/2

=0.583

请通过编辑我的代码或任何建议来帮助我做到这一点。

Answer 1

非常感谢。在另一个论坛的朋友的帮助下，我解决了这个问题。如果你认为这个解决方案对任何一个人来说都是必要的，我会发布这个解决方案。

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