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