SQL查询难题，高手快来

[乾朗]

要求：查询每个试题的正确率
--学员
create table student (
id number(19,0) not null,
name varchar2(255 char),
primary key (id)
)
--试题
create table exam_question (
id number(19,0) not null,
content clob,
score double precision,
primary key (id)
)
--选项
create table exam_choose (
id number(19,0) not null,
chooseItem varchar2(255 char),
contentString clob,
primary key (id)
)
-----------------------------------------------------
--试题对应的选项
create table exam_question_choose (
question_id number(19,0) not null,
choose_id number(19,0) not null,
primary key (question_id ,choose_id)
)
--试题对应的正确答案选项
create table exam_question_rightchoose (
question_id number(19,0) not null,
choose_id number(19,0) not null,
primary key (question_id ,choose_id)
)
--学生提交某个试题的答案
create table exam_exam_studentAnswer (
id number(19,0) not null,
question_id number(19,0),
student_id number(19,0),
primary key (id)
)
--试题的答案包含一或多个选项
create table exam_studentanswer_choose (
examStudentAnswer_id number(19,0) not null,
choose_id number(19,0) not null
)

要求：查询每个试题的正确率
结果结构如下：
question_id right_rate

[乾朗]

一条exam_exam_studentAnswer 记录代表
一个学员提交一个试题的答案
这个答案可有多个选项
exam_studentanswer_choose 就是这个答案和选择的选项关联表

[ngx20080110]

能提供一點測試數據嗎？

[乾朗]

表结构

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

[乾朗]

[Quote=引用 1 楼 hexiaojiao 的回复:]
一条exam_exam_studentAnswer 记录代表
一个学员提交一个试题的答案
这个答案可有多个选项
exam_studentanswer_choose 就是这个答案和选择的选项关联表
[/Quote]
请见3楼
顶起不沉帖！

[Cike121272604]

难得一见的题，现在忙，不过得先收藏。

[hexj002]

没有高手看到吗？帮顶

[redpan]

--试题答案包含一或多个选项
exam_studentanswer_choose (examStudentAnswer_id,choose_id)

这个表的描述不清楚，在我理解这个应该是学生做的试题的答案选择

[redpan]

如果是的话，examStudentAnswer_id是什么意思？
这张表应该提供->学生ID，试题ID，选择ID 这三个的唯一关系，才可以进行计算。

[乾朗]

针对消除数据冗余的设计

[乾朗]

[Quote=引用 6 楼 redpan 的回复:]
--试题答案包含一或多个选项
exam_studentanswer_choose (examStudentAnswer_id,choose_id)

这个表的描述不清楚，在我理解这个应该是学生做的试题的答案选择
[/Quote]
因为设计里试题不一定是不定项选择题，这个examStudentAnswer 是学员对一个试题提交的答案，这个答案可以有选项或其他类型试题关联，针对不定项选择题就有 exam_studentanswer_choose,这个答案可以有多个选中的选项,不定项选择题只有所有选项全选对才算正确

[乾朗]

存储过程实现也行，有兴趣的朋友可以试试

[hexj002]

[Quote=引用 6 楼 redpan 的回复:]
--试题答案包含一或多个选项
exam_studentanswer_choose (examStudentAnswer_id,choose_id)

这个表的描述不清楚，在我理解这个应该是学生做的试题的答案选择
[/Quote]

question_id, student_id,choose_id 可以放在一张表中但会有一个试题有多个选中答案，现在是分别放在 exam_studentAnswer和exam_studentanswer_choose中来减少数据冗余

[lg200015017]

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