表结构student (id,name)--学员exam_question (id,content)--试题exam_choose (id,content)--选项 ------------------------------------ --试题的选项 exam_question_choose (question_id,choose_id )--试题正确答案 exam_question_rightchoose (question_id, choose_id) --学生提交某个试题的答案 exam_studentAnswer(id, question_id, student_id)--试题答案包含一或多个选项 exam_studentanswer_choose (examStudentAnswer_id,choose_id) 要求:查询每个试题的正确率测试数据insert into student values(1,'Anna'); insert into student values(2,'Bob'); insert into student values(3,'Rose'); insert into student values(4,'Dulu'); insert into student values(5,'John');insert into exam_question(id) values(1); insert into exam_question(id) values(2); insert into exam_question(id) values(3); insert into exam_question(id) values(4);insert into exam_choose (id,chooseItem) values(1,'A'); insert into exam_choose (id,chooseItem) values(2,'B'); insert into exam_choose (id,chooseItem) values(3,'C'); insert into exam_choose (id,chooseItem) values(4,'A'); insert into exam_choose (id,chooseItem) values(5,'B'); insert into exam_choose (id,chooseItem) values(6,'D'); insert into exam_choose (id,chooseItem) values(7,'C');insert into exam_question_choose values(1,1); insert into exam_question_choose values(1,2); insert into exam_question_choose values(1,3); insert into exam_question_choose values(1,6); insert into exam_question_choose values(2,4); insert into exam_question_choose values(2,5); insert into exam_question_choose values(2,7); insert into exam_question_choose values(2,6); insert into exam_question_choose values(3,4); insert into exam_question_choose values(3,5); insert into exam_question_choose values(3,7); insert into exam_question_choose values(3,6); insert into exam_question_choose values(4,1); insert into exam_question_choose values(4,2); insert into exam_question_choose values(4,3); insert into exam_question_choose values(4,6);insert into exam_question_rightchoose values(1,3); insert into exam_question_rightchoose values(1,6); insert into exam_question_rightchoose values(2,5); insert into exam_question_rightchoose values(2,6); insert into exam_question_rightchoose values(3,5); insert into exam_question_rightchoose values(4,3);insert into exam_exam_studentAnswer values(1,1,1); insert into exam_exam_studentAnswer values(2,2,1); insert into exam_exam_studentAnswer values(3,3,1); insert into exam_exam_studentAnswer values(4,4,1); insert into exam_exam_studentAnswer values(5,1,2); insert into exam_exam_studentAnswer values(6,2,2); insert into exam_exam_studentAnswer values(7,3,2); insert into exam_exam_studentAnswer values(8,4,2); insert into exam_exam_studentAnswer values(9,1,3); insert into exam_exam_studentAnswer values(10,2,3); insert into exam_exam_studentAnswer values(11,3,3); insert into exam_exam_studentAnswer values(12,4,4);insert into exam_studentanswer_choose values(1,3); insert into exam_studentanswer_choose values(1,6); insert into exam_studentanswer_choose values(2,5); insert into exam_studentanswer_choose values(2,6); insert into exam_studentanswer_choose values(3,5); insert into exam_studentanswer_choose values(4,3);
Select aaa.QUESTION_ID,round(Count(Case When bbb.q_id Is Null Then 1 End)/Count(*)*100 ,2)||'%' right_rate From exam_exam_studentAnswer aaa Left Join ( ----谁答错了 Select Distinct decode(aa.CHOOSE_ID ,Null,bb.QUESTION_ID,aa.QUESTION_ID) q_id ,decode(aa.CHOOSE_ID ,Null,bb.STUDENT_ID,aa.STUDENT_ID) s_id From ( select a.QUESTION_ID,a.STUDENT_ID,b.CHOOSE_ID from exam_exam_studentAnswer a Join exam_question_rightchoose b On a.QUESTION_ID = b.QUESTION_ID ) aa Full Outer Join ( select a.QUESTION_ID,a.STUDENT_ID,d.CHOOSE_ID from exam_exam_studentAnswer a Left Join exam_studentanswer_choose d On a.Id = d.EXAMSTUDENTANSWER_ID ) bb On aa.QUESTION_ID = bb.QUESTION_ID And aa.STUDENT_ID = bb.STUDENT_ID And aa.CHOOSE_ID = bb.CHOOSE_ID Where aa.CHOOSE_ID Is Null Or bb.CHOOSE_ID Is Null ) bbb On aaa.QUESTION_ID = bbb.q_id And aaa.STUDENT_ID = bbb.s_id Group By aaa.QUESTION_ID
一个学员提交一个试题的答案
这个答案可有多个选项
exam_studentanswer_choose 就是这个答案和选择的选项关联表
------------------------------------
--试题的选项
exam_question_choose (question_id,choose_id )--试题正确答案
exam_question_rightchoose (question_id, choose_id) --学生提交某个试题的答案
exam_studentAnswer(id, question_id, student_id)--试题答案包含一或多个选项
exam_studentanswer_choose (examStudentAnswer_id,choose_id)
要求:查询每个试题的正确率测试数据insert into student values(1,'Anna');
insert into student values(2,'Bob');
insert into student values(3,'Rose');
insert into student values(4,'Dulu');
insert into student values(5,'John');insert into exam_question(id) values(1);
insert into exam_question(id) values(2);
insert into exam_question(id) values(3);
insert into exam_question(id) values(4);insert into exam_choose (id,chooseItem) values(1,'A');
insert into exam_choose (id,chooseItem) values(2,'B');
insert into exam_choose (id,chooseItem) values(3,'C');
insert into exam_choose (id,chooseItem) values(4,'A');
insert into exam_choose (id,chooseItem) values(5,'B');
insert into exam_choose (id,chooseItem) values(6,'D');
insert into exam_choose (id,chooseItem) values(7,'C');insert into exam_question_choose values(1,1);
insert into exam_question_choose values(1,2);
insert into exam_question_choose values(1,3);
insert into exam_question_choose values(1,6);
insert into exam_question_choose values(2,4);
insert into exam_question_choose values(2,5);
insert into exam_question_choose values(2,7);
insert into exam_question_choose values(2,6);
insert into exam_question_choose values(3,4);
insert into exam_question_choose values(3,5);
insert into exam_question_choose values(3,7);
insert into exam_question_choose values(3,6);
insert into exam_question_choose values(4,1);
insert into exam_question_choose values(4,2);
insert into exam_question_choose values(4,3);
insert into exam_question_choose values(4,6);insert into exam_question_rightchoose values(1,3);
insert into exam_question_rightchoose values(1,6);
insert into exam_question_rightchoose values(2,5);
insert into exam_question_rightchoose values(2,6);
insert into exam_question_rightchoose values(3,5);
insert into exam_question_rightchoose values(4,3);insert into exam_exam_studentAnswer values(1,1,1);
insert into exam_exam_studentAnswer values(2,2,1);
insert into exam_exam_studentAnswer values(3,3,1);
insert into exam_exam_studentAnswer values(4,4,1);
insert into exam_exam_studentAnswer values(5,1,2);
insert into exam_exam_studentAnswer values(6,2,2);
insert into exam_exam_studentAnswer values(7,3,2);
insert into exam_exam_studentAnswer values(8,4,2);
insert into exam_exam_studentAnswer values(9,1,3);
insert into exam_exam_studentAnswer values(10,2,3);
insert into exam_exam_studentAnswer values(11,3,3);
insert into exam_exam_studentAnswer values(12,4,4);insert into exam_studentanswer_choose values(1,3);
insert into exam_studentanswer_choose values(1,6);
insert into exam_studentanswer_choose values(2,5);
insert into exam_studentanswer_choose values(2,6);
insert into exam_studentanswer_choose values(3,5);
insert into exam_studentanswer_choose values(4,3);
exam_studentanswer_choose (examStudentAnswer_id,choose_id)这个表的描述不清楚,在我理解这个应该是学生做的试题的答案选择
这张表应该提供->学生ID,试题ID,选择ID 这三个的唯一关系,才可以进行计算。
question_id, student_id,choose_id 可以放在一张表中但会有一个试题有多个选中答案,现在是分别放在 exam_studentAnswer和exam_studentanswer_choose中来减少数据冗余
From exam_exam_studentAnswer aaa
Left Join (
----谁答错了
Select Distinct decode(aa.CHOOSE_ID ,Null,bb.QUESTION_ID,aa.QUESTION_ID) q_id
,decode(aa.CHOOSE_ID ,Null,bb.STUDENT_ID,aa.STUDENT_ID) s_id
From
(
select a.QUESTION_ID,a.STUDENT_ID,b.CHOOSE_ID from exam_exam_studentAnswer a
Join exam_question_rightchoose b On a.QUESTION_ID = b.QUESTION_ID
) aa
Full Outer Join
(
select a.QUESTION_ID,a.STUDENT_ID,d.CHOOSE_ID from exam_exam_studentAnswer a
Left Join exam_studentanswer_choose d On a.Id = d.EXAMSTUDENTANSWER_ID
) bb
On aa.QUESTION_ID = bb.QUESTION_ID And aa.STUDENT_ID = bb.STUDENT_ID And aa.CHOOSE_ID = bb.CHOOSE_ID
Where aa.CHOOSE_ID Is Null Or bb.CHOOSE_ID Is Null
) bbb
On aaa.QUESTION_ID = bbb.q_id And aaa.STUDENT_ID = bbb.s_id
Group By aaa.QUESTION_ID