select x.mytype 题型, x.title 题目内容, x.answer 答案,x.optionnum 选项数,选项A,选项B,选项C,选项D,选项E,选项F, y.totalcount 回答数, y.rightcount 正确数,trunc(y.rightcount*100/y.totalcount,2)||'%' 百分比
from (select H.id,
H.Title,
case
when H.Type = 1 then
'单选题'
when H.Type = 2 then
'多选题'
when H.Type = 3 then
'判断题'
when H.Type = 4 then
'填空题'
when H.Type = 5 then
'问答题'
end mytype,
H.Answer,
H.Optionnum,
MAX(DECODE(Q.Orderby, 1, Q.Title, '')) 选项A,
MAX(DECODE(Q.Orderby, 2, Q.Title, '')) 选项B,
MAX(DECODE(Q.Orderby, 3, Q.Title, '')) 选项C,
MAX(DECODE(Q.Orderby, 4, Q.Title, '')) 选项D,
MAX(DECODE(Q.Orderby, 5, Q.Title, '')) 选项E,
MAX(DECODE(Q.Orderby, 6, Q.Title, '')) 选项F
from tbl_lib_question H, tbl_lib_option Q
where Q.Questionid = H.id
and Q.Questionid in
(select B.questionid questionid
from tbl_d_answer_examplan A, tbl_d_answer_question B
where A.Examplanid = 562
and A.Id = B.Answerexamplanid
group by B.Questionid)
group by H.id, H.Title, H.Type, H.Answer,H.Optionnum
order by H.id) X,
(select M.questionid,
M.totalcount,
case
when N.rightcount is null then
0
else
rightcount
end rightcount
from (select B.questionid questionid, count(*) totalcount
from tbl_d_answer_examplan A, tbl_d_answer_question B
where A.Examplanid = 562
and A.Id = B.Answerexamplanid
group by B.Questionid) M,
(select B.questionid questionid, count(*) rightcount
from tbl_d_answer_examplan A, tbl_d_answer_question B
where A.Examplanid = 562
and A.Id = B.Answerexamplanid
and B.Score = B.Gainscore
group by B.Questionid) N
where M.questionid = N.questionid(+)
order by questionid) Y
where X.id = Y.questionid